[thelist] MySQL query with two left joins
Sarah
poohbear at designshift.com
Wed Jul 2 10:41:09 CDT 2003
> > >Unfortunately, this query BEGS to have a subselect in the SELECT clause.
> >
> > Joshua,
> >
> > 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.
Joshua,
Unfortunately, we are running version 4.0 until 4.1 is released as a
non-alpha or beta. Rather than spend any more time trying to figure this
out (before we install 4.1), I am just going to include one of the counts
in the main query, and then, as I loop through the results, run individual
queries for the other count.
Thanks so much to everyone who made a suggestion for how to make this work!
Sarah
More information about the thelist
mailing list