[thelist] MySQL query with two left joins
Joshua Olson
joshua at waetech.com
Mon Jun 30 15:20:31 CDT 2003
----- 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
More information about the thelist
mailing list