With Mysql - Problem with left join three tables getting duplicated group_concat results

Question

I have the following tables that I want to join with left join and group_contact

maintable

+--------+---------+
| cedula | nombre  |
+--------+---------+
| 002    | Eduardo |
| Hola   | Carlos  |
+--------+---------+

maintable_msel_sex1

+--------+------+--------------------------------------+
| cedula | sex1 | rowuuid                              |
+--------+------+--------------------------------------+
| 002    |    1 | 4ccc2b41-e72a-4d55-b167-17a41c18b29b |
| 002    |    2 | 89323daa-4ad4-4b43-9ecc-22d4fdd8fef1 |
| Hola   |    1 | 9e7b4da0-2a93-4c82-80eb-3172bb163536 |
+--------+------+--------------------------------------+

maintable_msel_sex2

+--------+------+--------------------------------------+
| cedula | sex2 | rowuuid                              |
+--------+------+--------------------------------------+
| 002    |    2 | 122123d3-aa5e-42b5-8ddd-0e5d700bd92c |
| 002    |    1 | 462c8138-37b1-42bd-a0c8-4a63d9e67dda |
| Hola   |    1 | 8d96c9c8-4633-4425-8f27-59591bee8954 |
| Hola   |    2 | bbe42e16-fc9d-4e1d-825d-1eb59161f2fb |
+--------+------+--------------------------------------+

maintable_msel_crops

+--------+-------+--------------------------------------+
| cedula | crops | rowuuid                              |
+--------+-------+--------------------------------------+
| 002    |     3 | 404e7f5c-fb07-4d43-85a8-6c86c5407546 |
| Hola   |     3 | 423d1d06-9be4-4d47-a5be-8407db4c258b |
| 002    |     4 | 6d27aa7f-b6d2-426b-9c05-c20f88d97d7b |
| 002    |     1 | 7673127b-72ed-45fd-8688-8f6942abeaa0 |
| Hola   |     1 | 80f84fbe-ad47-4178-8577-eaa82562abaa |
+--------+-------+--------------------------------------+

I try to join them using the following select:

SELECT maintable.cedula,GROUP_CONCAT(maintable_msel_sex1.sex1) as sex1, GROUP_CONCAT(maintable_msel_sex2.sex2) as sex2, GROUP_CONCAT(maintable_msel_crops.crops) as crops
FROM maintable 
LEFT JOIN maintable_msel_sex1 ON maintable.cedula = maintable_msel_sex1.cedula
LEFT JOIN maintable_msel_sex2 ON maintable.cedula = maintable_msel_sex2.cedula
LEFT JOIN maintable_msel_crops ON maintable.cedula = maintable_msel_crops.cedula
GROUP BY  maintable.cedula;

But I get:

+--------+-------------------------+-------------------------+-------------------------+
| cedula | sex1                    | sex2                    | crops                   |
+--------+-------------------------+-------------------------+-------------------------+
| 002    | 1,1,1,1,1,1,2,2,2,2,2,2 | 1,1,1,2,2,2,1,1,1,2,2,2 | 1,3,4,1,3,4,1,3,4,1,3,4 |
| Hola   | 1,1,1,1                 | 1,1,2,2                 | 1,3,1,3                 |
+--------+-------------------------+-------------------------+-------------------------+

I would like to get:

+--------+-------------------------+-------------------------+-------------------------+
| cedula | sex1                    | sex2                    | crops                   |
+--------+-------------------------+-------------------------+-------------------------+
| 002    | 1,2                     | 1,2                     | 1,3,4                   |
| Hola   | 1                       | 1,2                     | 1,3                     |
+--------+-------------------------+-------------------------+-------------------------+

I tried with INNER JOIN and RIGHT JOIN and by using only one JOIN with several tables in () but I just not get such a result.

I could use distinct in the group_concat as a patch but I don't understand why I am getting duplicated results.

Any help is appreciated

I fail to see how 1f2fb is less comprehensible to the casual observer than bbe42e16-fc9d-4e1d-825d-1eb59161f2fb, but maybe that's just me. Anyway, for further help see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, May 28 '21 at 22:37
"I could use distinct in the group_concat as a patch but I don't understand why I am getting duplicated results." - what you are group_concating is one row per maintable per maintable_msel_sex1 per maintable_msel_sex2 per maintable_msel_crops. so if you have 1 maintable row with 2 sex1 rows and 2 sex2 rows and 3 crops rows, that's 12 distinct combinations of rows that you are grouping together (002/1/1/1, 002/1/1/3, 002/1/1/4, 002/1/2/1, 002/1/2/3, 002/1/2/4, 002/2/1/1, 002/2/1/3, 002/2/1/4, 002/2/2/1, 002/2/2/3, 002/2/2/4). — ysth, May 28 '21 at 23:24

score 1 · Answer 1 · answered May 28 '21 at 22:36

If I understand your problem correctly you want to only have each value once in the GROUP_CONCAT. For this you can use DISTINCT:

SELECT
  maintable.cedula,
  GROUP_CONCAT(DISTINCT maintable_msel_sex1.sex1) as sex1,
  GROUP_CONCAT(DISTINCT maintable_msel_sex2.sex2) as sex2,
  GROUP_CONCAT(DISTINCT maintable_msel_crops.crops) as crops
FROM maintable 
  LEFT JOIN maintable_msel_sex1 ON maintable.cedula = maintable_msel_sex1.cedula
  LEFT JOIN maintable_msel_sex2 ON maintable.cedula = maintable_msel_sex2.cedula
  LEFT JOIN maintable_msel_crops ON maintable.cedula = maintable_msel_crops.cedula
GROUP BY  maintable.cedula;

See MySQL DISTINCT on a GROUP_CONCAT() and http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat for more information

With Mysql - Problem with left join three tables getting duplicated group_concat results

1 Answers1