php mysql grouping result

Question

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.....

Sorry, had to make an edit. I think that is what you were looking for. — JPR
– JPR, Commented Nov 29, 2012 at 20:51

JPR · Accepted Answer · 2012-11-29 21:06:17Z

4

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

edited Nov 29, 2012 at 21:06

answered Nov 29, 2012 at 20:31

JPR

8694 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ytse_jam Over a year ago

hi JPR, your answer is very well working, i just had to add "as subs" SELECT stud.section, stud.name, group_concat(subj.subject, '') as subs FROM table_students stud, thanks you. JOIN table_subjects subj ON stud.student_id = subj.student_id GROUP BY stud.name

phpisuber01 · Accepted Answer · 2012-11-29 20:30:49Z

0

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.

answered Nov 29, 2012 at 20:30

phpisuber01

7,7453 gold badges25 silver badges26 bronze badges

Collectives™ on Stack Overflow

php mysql grouping result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related