[RESOLVED] Left Join Mutliple Tables

[Shawazi]

I have this query:

SELECT articles . *, article_tags.art_id, COUNT( article_tags.id ) AS appres, COUNT( article_comments.art_id ) AS comms
FROM articles
LEFT JOIN (
article_tags, article_comments
) ON ( article_tags.art_id = articles.id
AND article_comments.art_id = articles.id
AND article_tags.art_id = article_comments.art_id )
GROUP BY id

The simplified table structure is like

articles     article_comments          article_tags
---------    ----------------------     --------------
id               id                       id
                 art_id                    art_id

If I left join just articles to comments or just articles to tags, it shows the correct count number. When I join both it completely messes up the number in no logical way to me.

[Shawazi]

SELECT
t1.published,
t1.id,
t1.title,
t1.creat_id,
t1.auth_id,
t1.teaser,
t1.posted_on,
t1.image_loc,
COUNT( t2.id ) AS appres,
COUNT( t3.id ) AS comms
FROM articles t1
LEFT JOIN article_appreciations t2 ON t2.art_id = t1.id

LEFT JOIN article_comments t3 ON t3.art_id = t1.id

GROUP BY t1.id

shows correct value for t3 but not t2

[Roger_Ramjet]

1:M returns the combined results from the first join, with rows from the 1 table duplicated and concatenated to each matching row in the M table. This is then joined to the next table with the same result so that you now get rows from the second table also being duplicated when there is more than one match.

[Shawazi]

Using subqueries was the answer