[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