[thelist] MySQL query with two left joins
Sarah
poohbear at designshift.com
Wed Jul 2 08:30:01 CDT 2003
> > 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,
I get the exact same results even if my GROUP BY statement is just "GROUP
BY c.Title", so that definitely isn't the problem.
I'm not sure if MySQL supports the use of a sub-select in the SELECT
clause, but if it does, can you give me an idea of how to do this?
Sarah
More information about the thelist
mailing list