CODEWITHSUNDEEP
mysqlleft-joingroup-concat
Sandip Ransing
Sandip Ransing

Reputation: 7733

group_concat on two different tables gives duplicate result on second table

consider three entities as student, course, subject Below are the associations - 

student has_many courses,
student has_many subjects.

Now i want to fetch student records with subject names and course names using mysql group_concat, left join on courses, left join on subjects and group_by student_id.

Problem is that group_concat('subjects.name') as subject_names gives me duplicate entries of subjects but group_concat('students.name') as student_names gives unique names.

Why ??

Upvotes: 1

Views: 2098

Answers (3)

ruuter
ruuter

Reputation: 2533

I know I'm probably driving this a bit too off topic, but because searching answer from google have directed me here for several times already I'd like to share my solution, for a bit more complicated similar problem.

The GROUP_CONCAT(DISTINCT ...) solution as gbn pointed out, is great, until you actually have multiple equal values or almost equal like á and a.

I left out the distinct keyword from query and solved the problem with PHP. If you only need to distinguish á from a, simple array_unique will do the trick.

Unfortunately I was not so lucky and I also had exactly equal values which I needed to keep. Consider sample values returned from database query group_concat field exploded into array:

$values = array( 'Value1','Value1','Value2','Value2','Value2','Value2' );

Now somehow distinguish how many duplicates are you dealing with. I did the following:

$x=0;
$first = reset($values);
while($first === $values[$x]) $x++;

Above solution works only if your actual first and second value is never same, which in my case was true. If that's not the case with you, figure out some other way to know how many duplicates are you dealing with. Finally just unset all extra values with a help of modulo:

foreach($values as $k => $v){
       if($k%$x !== 0) unset($values[$k]);
}

Thats it. Printing $values now will give you:

Array
(
    [0] => Value1
    [2] => Value2
    [4] => Value2
)

Upvotes: 0

gbn
gbn

Reputation: 432230

The 2 left joins are multiplying rows via Cartesian product of the child rows per student

Example

  • Student 1 has 3 courses and 2 subjects
  • Generates 6 rows for Student 1
  • Gives one course value per subject = each course repeated twice
  • Gives one subject value per course = each subject repeated thrice

To fix:

Option 1: Use GROUP_CONCAT(DISTINCT ...) as per MySQL docs

In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause.

Option 2: Use a UNION ALL + derived table

SELECT
    Student, MAX(CourseConcat), MAX(SubjectConcat)
FROM
    (
    -- 2 separate SELECTs here
    .. student LEFT JOIN course ...
    UNION ALL
    .. student LEFT JOIN subjects...
    ) T
GROUP BY
   Student

The 2nd option may be better albeit more complex because you have less intermediate rows to process with DISTINCT

Upvotes: 9

DarkAjax
DarkAjax

Reputation: 16223

Following you logic, group_concat('subjects.name') as subject_names gives you duplicate entries because there's possibly more than 1 subject for each student, so you're getting a duplicate record for every student record on the subject table, while group_concat('students.name') as student_names (I presume) has 1 record per student.

Upvotes: 0

Related Questions