[thelist] MySQL query with two left joins
Gary McPherson
genyus at ingenyus.net
Wed Jul 2 12:22:27 CDT 2003
> 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!
Before you give up hope, Sarah - I believe I have the answer to your
problem. You can achieve the same results of sub-queries using temporary
tables. I think this query should do the job:
CREATE TEMPORARY TABLE tmpCategories
SELECT c.CategoryID,c.ParentCategoryID,c.Title, count(dc.DocumentID) as
DocCount
FROM Categories c
LEFT JOIN jn_DocumentCategories dc
ON c.CategoryID = dc.CategoryID
WHERE c.ParentCategoryID = 5
GROUP BY c.CategoryID
ORDER BY c.Title;
SELECT c.CategoryID,
c.ParentCategoryID,c.Title,DocCount,count(c2.CategoryID) as CatCount
FROM tmpCategories c
LEFT JOIN Categories c2
ON c2.ParentCategoryID = c.CategoryID
GROUP BY c.CategoryID;
DROP TABLE tmpCategories;
Let me know how you get on.
Gary
More information about the thelist
mailing list