[thelist] MySQL query with two left joins

[Joshua Olson]

----- Original Message ----- 
From: "Sarah" <poohbear at designshift.com>
Sent: Monday, June 30, 2003 10:50 AM


>     SELECT c.CategoryID,
>            c.ParentCategoryID,
>            c.Title,
>            COUNT(cc.CategoryID) AS SubCatCount,
>            COUNT(dc.DocumentID) AS DocCount
>       FROM Categories c
> LEFT JOIN Categories cc
>         ON c.CategoryID = cc.ParentCategoryID
> LEFT JOIN jn_DocumentCategories dc
>         ON c.CategoryID = dc.CategoryID
>      WHERE c.ParentCategoryID = 2
>   GROUP BY c.CategoryID, c.ParentCategoryID, c.Title
>   ORDER BY c.Title

Sarah,

I'm no MySQL guru, but the most obvious reason I don't think this will work
is because you have a GROUP BY on the PK (c.CategoryID).  This means that
the COUNTs will be 1, regardless of what else you do.

Unfortunately, this query BEGS to have a subselect in the SELECT clause.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168