Multiple many-to-many JOINs in a single mysql query without Cartesian Product
Posted
by VWD
on Stack Overflow
See other posts from Stack Overflow
or by VWD
Published on 2010-06-15T05:54:45Z
Indexed on
2010/06/15
6:02 UTC
Read the original article
Hit count: 306
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?
© Stack Overflow or respective owner