MySql select data from multiple tables - several sub records expected

41 views Asked by At

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!

1

There are 1 answers

0
juergen d On
SELECT t1.classid, 
       group_concat(t1.sectionid order by t1.sectionid), 
       group_concat(t2.subjectid order by t1.sectionid), 
       group_concat(t3.examid order by t1.sectionid)
    FROM `tbl_A` As t1
JOIN `tbl_B`  t2 ON t1.classid = t2.classid
JOIN `tbl_C`  t3 ON  t1.classid = t3.classid
group by t1.classid