[thelist] MySQL query with two left joins
Sarah
poohbear at designshift.com
Mon Jun 30 09:50:02 CDT 2003
I am currently working on a site which is basically a large repository of
documents, sorted into a hierarchy of categories. So, each category can
have multiple children, and each category has one parent. Each document can
belong to multiple categories. Here are the important fields in the tables
in question:
CATEGORIES
----------
CategoryID
ParentCategoryID
Title
DOCUMENTS
---------
DocumentID
Title
JN_DOCUMENTCATEGORIES
---------------------
DocumentID
CategoryID
In the admin system for the site, I am trying to output a list of child
categories of one parent. I would like to include the count of the
categories' documents and the count of the categories' sub-categories in
this list as well, but I can't quite figure out how to make my left joins
cooperate. Here is the query I am using:
SELECT c.CategoryID,
c.ParentCategoryID,
c.Title,
COUNT(cc.CategoryID) AS SubCatCount,
COUNT(dc.DocumentID) AS DocCount
FROM Categories c
LEFT JOIN Categories cc
ON c.CategoryID = cc.ParentCategoryID
LEFT JOIN jn_DocumentCategories dc
ON c.CategoryID = dc.CategoryID
WHERE c.ParentCategoryID = 2
GROUP BY c.CategoryID, c.ParentCategoryID, c.Title
ORDER BY c.Title
When I run this query, any row that should have a number greater than 0 for
SubCatCount and DocCount is returning the product of SubCatCount * DocCount
in both columns. I hope my description of the problem makes sense! Any help
in this would be appreciated.
TIA
Sarah
More information about the thelist
mailing list