CODEWITHSUNDEEP
mysqlsqldatabaseselect
Nick
Nick

Reputation: 201

Multiple LEFT JOIN in multiple tables

I have the following SQL select:

SELECT s.*,
       GROUP_CONCAT(CONCAT_WS(':', m.type, m.id, m.filename) SEPARATOR ',') AS multimedia,
       GROUP_CONCAT(CONCAT_WS(':', c.id) SEPARATOR ',') AS categories
FROM sections s 


LEFT JOIN sections_multimedia sm
     ON s.id = sm.section_id 
LEFT JOIN multimedia m
     ON sm.multimedia_id = m.id 


LEFT JOIN sections_categories sc
     ON s.id = sc.section_id 
LEFT JOIN categories c
     ON sc.category_id = c.id


WHERE s.id = s.id
GROUP BY s.id
ORDER BY s.position, s.id ASC;

As a result, the field 'categories' has the correct values, which are(2,3), but unfortunately repeated many times! (the result is: 2,2,2,2,2,2,3,3,3,3,3,3)

What is wrong with my select?

Thank you for your replies!

Upvotes: 0

Views: 2697

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

When looking for aggregates from different tables, you should always aggregate before joining:

SELECT 
  s.*,
  mul.multimedia,
  cat.categories 
FROM sections s 
LEFT JOIN
(
  SELECT
    sm.section_id,
    GROUP_CONCAT(CONCAT_WS(':', m.type, m.id, m.filename) SEPARATOR ',') AS multimedia
  FROM sections_multimedia sm
  JOIN multimedia m ON sm.multimedia_id = m.id 
  GROUP BY sm.section_id
) mul ON s.id = mul.section_id
LEFT JOIN
(
  SELECT
    sc.section_id,
    GROUP_CONCAT(CONCAT_WS(':', c.id) SEPARATOR ',') AS categories
  FROM sections_categories sc
  JOIN categories c ON sc.category_id = c.id
  GROUP BY sc.section_id
) cat ON s.id = cat.section_id;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can get unique values by using DISTINCT:

SELECT s.*,
       GROUP_CONCAT(DISTINCT CONCAT_WS(':', m.type, m.id, m.filename) SEPARATOR ',') AS multimedia,
       GROUP_CONCAT(DISTINCT CONCAT_WS(':', c.id) SEPARATOR ',') AS categories

This is actually a work-around. Your real problem is that multiple joins are creating a Cartesian product of value for each row in the result set. It might be better to aggregate before doing the joins, rather than removing the duplicates when aggregating.

Upvotes: 3

Related Questions