Problem with Count() in MySQL 1

[SyberCoda]

This thing has been driving me nuts. Any help would be much appreciated! I have the following query...

SELECT tbl_Cats.*, tbl_OpenProjects.St, Count(tbl_OpenProjects.CatID) AS NoOfProjects
FROM tbl_Cats
LEFT JOIN tbl_OpenProjects ON tbl_Cats.CatID = tbl_OpenProjects.CatID
WHERE tbl_OpenProjects.St = '#Cookie.State#'
GROUP BY tbl_OpenProjects.St, tbl_Cats.CatID, tbl_OpenProjects.CatID
ORDER BY Title

tbl_Cats is the category table. This query lists all of the categories and a count for the # of Open Projects per category. But when I add "WHERE tbl_OpenProjects.St = '#Cookie.State#'" it only lists categories which have 1 or more Open Projects and does not include the categories that have 0 Projects. I'm trying to get it to list all categories with a count of the number of open projects but only count open projects that belong to a particular state. For some reason, when I try to do this it also affects the category list. Any suggestions? I using MySQL
Thanks in advanced!

 

[PHV]

You may try this:
SELECT C.CatID, C.Title, O.St, Count(O.CatID) AS NoOfProjects
FROM tbl_Cats C LEFT JOIN tbl_OpenProjects O
ON C.CatID = O.CatID AND O.St = '#Cookie.State#'
GROUP BY C.CatID, C.Title, O.St
ORDER BY C.Title

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SyberCoda]

Thanks a million! I will try that. Didn't know you could add "AND O.St = '#Cookie.State#'" to a LEFT JOIN. Koool!

 

[PHV]

You can in ANSI SQL, don't know in MySQL ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

what PHV is trying to say is that you posted in the ANSI SQL forum, so you got an ANSI SQL answer

PHV, yes, that works in MySQL too ;-)

r937.com | rudy.ca

 

[SyberCoda]

I just tried it and worked like a charm ; ) Thanks again!