[thelist] MySQL query with two left joins

[Joshua Olson]

----- Original Message ----- 
From: "Sarah" <poohbear at designshift.com>
Sent: Wednesday, July 02, 2003 9:30 AM


> >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.

Are the titles all unique?

> 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?

SELECT c.CategoryID,
           c.Title,
(SELECT Count(*)
FROM jn_DocumentCategories dc
WHERE dc.CategoryID = c.CategoryID) AS documents_in_category
FROM Categories c
WHERE 2 IN (c.ParentCategoryID, c.CategoryID)

This query will return the parent category, and all immediate children
categories, one per row.  The documents_in_category column will hold the
count of the documents in that category.

The subselect is the inner SELECT clause within the outer SELECT clause.
Note that it's using c.CategoryID from the outer SELECT in its conditional.

I'll leave it to you to work out the ORDER BY clause that will make the
parent category move to the first record.

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