mysql - group_concat on two different tables gives duplicate result on second table -

consider 3 entities student, course, subject below associations -

student has_many courses, student has_many subjects. 

now want fetch student records subject names , course names using mysql group_concat, left join on courses, left join on subjects , group_by student_id.

problem group_concat('subjects.name') subject_names gives me duplicate entries of subjects group_concat('students.name') student_names gives unique names.

why ??

the 2 left joins multiplying rows via cartesian product of child rows per student

example

• student 1 has 3 courses , 2 subjects
• generates 6 rows student 1
• gives 1 course value per subject = each course repeated twice
• gives 1 subject value per course = each subject repeated thrice

to fix:

option 1: use group_concat(distinct ...) per mysql docs

in mysql, can concatenated values of expression combinations. eliminate duplicate values, use distinct clause.

option 2: use union + derived table

select     student, max(courseconcat), max(subjectconcat)     (     -- 2 separate selects here     .. student left join course ...     union     .. student left join subjects...     ) t group    student 

the 2nd option may better albeit more complex because have less intermediate rows process distinct