Multiple many-to-many JOINs in a single mysql query without Cartesian Product
- by VWD
At the moment I can get the results I need with two seperate SELECT statements
SELECT
COUNT(rl.refBiblioID)
FROM biblioList bl
LEFT JOIN refList rl ON bl.biblioID = rl.biblioID
GROUP BY bl.biblioID
SELECT
GROUP_CONCAT(
CONCAT_WS( ':', al.lastName, al.firstName )
ORDER BY al.authorID )
FROM biblioList bl
LEFT JOIN biblio_author ba ON ba.biblioID = bl.biblioID
JOIN authorList al ON al.authorID = ba.authorID
GROUP BY bl.biblioID
Combining them like this however
SELECT
GROUP_CONCAT(
CONCAT_WS( ':', al.lastName, al.firstName )
ORDER BY al.authorID ),
COUNT(rl.refBiblioID)
FROM biblioList bl
LEFT JOIN biblio_author ba ON ba.biblioID = bl.biblioID
JOIN authorList al ON al.authorID = ba.authorID
LEFT JOIN refList rl ON bl.biblioID = rl.biblioID
GROUP BY bl.biblioID
causes the author result column to have duplicate names. How can I get the desired results from one SELECT statement without using DISTINCT? With subqueries?
