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: 315

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

Related posts about mysql

Related posts about join