I have a feeling I might have got the table structure wrong, so any help would be greatly appreciated.
I have, say, three tables
tbl_A (has class and section association)
ID
classid
sectionid
tbl_B (has subject and class association)
ID
classid
subjectid
tbl_C (has examination and class association)
ID
classid
examid
I need to display the records in following format
Class | Section (s) | Subject (s) | Examination (s)
---------------------------------------------------
Grade 1 | A, B, C, D | English, Maths, Drawing... | Assessment 1, Assessment 2...
SQL SCRIPT
SELECT DISTINCT t1.classid, t1.sectionid, t2.subjectid, t3.examid
FROM `tbl_A` As t1
JOIN `tbl_B` t2 ON t1.classid = t2.classid
JOIN `tbl_C` t3 ON t1.classid = t3.classid
Can the above in achieve with one SQL QUERY (with join etc) or do I need to create separate SQL with recordsets to loop through (for example, Grade 1 will have multiple subjects etc)
Thanks heaps in advance!