[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