Anyone can help me this?
i have two tables, they are linked by student_id.
im looking for a more cleaner/single query that can output the sample below,
although i achieved this by inserting another query(with group_concat for the subjects)
inside the while loop query for the section and name.
table_students
student_id, section, name
table_subjects
student_id, subject
now i want the output to be like this.
student_id section name subject
100 A john algebra, trigo, geometry
101 A peter trigo, geometry,
102 B alice literature, algebra
103 B james trigo
thank you in advance.
by the way, i forgot to give some more details, in my subjects table, the subjects is per row, like this
student_id subject
100 algebra
100 tigo
100 geometry
101 trigo
101 geometry
102 literature
and so on.....
SELECT
stud.section, stud.name, group_concat(subj.subject, '')
FROM table_students stud
JOIN table_subjects subj
ON stud.student_id = subj.student_id
GROUP BY stud.name
Try this MySQL Query:
SELECT stu.section, stu.name, sub.subject
FROM table_students stu, table_subject sub
WHERE stu.student_id=sub.student_id
GROUP BY stu.section
Should do exactly what you need. Selects the relevant data by student_id in both tables, then specifies which fields to return. Finally, grouping them by section.