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!
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;
Эта статья взята из Интернета, укажите источник при перепечатке.
Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.
я говорю два предложения