[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