Hello everyone,
I am retrieving records from database using select statement, but because i stored value property in the table i have to join multiple tables in order to display the text property, before joining the other tables, the result came out fine returning four rows, after joining the table i got 14 rows, i tried to debug it, i didnt seem to see where the issue is coming from. I will appreciate if anyone could assist.
What I have tried:
Script before joining tables:
SELECT DISTINCT
,rs.[ResultID]
,rs.[StudentNo_]
,rs.[House]
,rs.[DOB]
,rs.[AcademicSession]
,rs.[Term]
,rs.[TeacherRemark]
,rs.[MarkObtainable]
,rs.[MarkObtained]
,rs.[Subject]
,rs.[School]
,rs.[CATest1]
,rs.[CATest2]
,rs.[CATest3]
,rs.[CATest4]
,rs.[CATest5]
,rs.[ExamScore]
,rs.[HeadTeacherRemark]
,rs.[CATotal]
,rs.[Grade]
,rs.[Remark]
,rs.[Class]
,rs.[ClassAverage]
FROM [Results] rs
WHERE rs.[StudentNo_] = @StudentNumber AND rs.[Term] = @Term AND rs. [AcademicSession] = @AcademicSession AND rs.[Class] = @Class AND rs.[School] = @School
script after joining tables:
SELECT DISTINCT pss.[Code] SubCode
,ss.[Description] SubjectName
,pss.[Subject Code] SubjectCode
,pss.[Core Subject] CoreSubject
,pss.[CA Calculator] CACalculator
,pss.[Grade Display] GradeDisplay
,pss.[Term Total Calculator] TermTotalCalculator
,rs.[ResultID]
,rs.[StudentNo_]
,sd.[FullName]
,sd.[ImagePath]
,sd.[Gender]
,sd.[House]
,sd.[Date Of Birth] DOB
,ass.[Description] AcademicSession
,rs.[Term]
,rs.[TeacherRemark]
,rs.[MarkObtainable]
,rs.[MarkObtained]
,rs.[Subject]
,sl.[Name] School
,rs.[CATest1]
,rs.[CATest2]
,rs.[CATest3]
,rs.[CATest4]
,rs.[CATest5]
,rs.[ExamScore]
,rs.[HeadTeacherRemark]
,rs.[CATotal]
,rs.[Grade]
,rs.[Remark]
,rs.[Class]
,rs.[ClassAverage]
FROM [Results] rs
INNER JOIN [PrimarySubjectSetUp] pss ON rs.[Subject] = pss.[Subject Code]
INNER JOIN [SchoolList] sl ON rs.[School] = sl.[Code]
INNER JOIN [Student] sd ON rs.StudentNo_ = sd.No_
INNER JOIN [School Subjects] ss ON pss.[Subject Code] = ss.[Code]
INNER JOIN [AcademicSession] ass ON rs.[AcademicSession] = ass.Code
WHERE rs.[StudentNo_] = @StudentNumber AND rs.[Term] = @Term AND rs.[AcademicSession] = @AcademicSession AND rs.[Class] = @Class AND rs.[School] = @School
ORDER BY pss.[Subject Code] ASC