Search code examples
mysqljoingroup-concat

Double results in GROUP_CONCAT with double JOIN in MySQL

I have the following tables:

products

  • Fields: id, title
  • Values: (1,'Product 1')

table1

  • Fields: id, idProduct
  • Values: (1,1),(2,1)

table2

  • Fields: id, idProduct
  • Values: (3,1),(4,1)

And the following query:

SELECT
  p.*,
  GROUP_CONCAT(t1.id ORDER BY t2.id),
  GROUP_CONCAT(t2.id ORDER BY t2.id)
FROM
  products p
JOIN table1 t1 ON p.id=t1.idProduct
JOIN table2 t2 ON p.id=t2.idProduct
GROUP BY
  p.id

Expected result is:

1 | Product 1 | 1,2     | 3,4

Unfortunately I get:

1 | Product 1 | 1,1,2,2 | 3,3,4,4
Solution

  • You need to add DISTINCT:

    GROUP_CONCAT(DISTINCT t1.id ORDER BY t2.id),