chodo baggins chodo baggins - 2 months ago 8
MySQL Question

Left Join with Count and Where Clause on right side

I have the following query:

SELECT G.groupCode, G.groupName, COUNT(C.cdCode) AS numberOfTops10CDs FROM musicalgroup G
LEFT OUTER JOIN cd C ON C.groupCode = G.groupCode
WHERE C.cdCode IN
(SELECT cdCode FROM topcds WHERE rating <= 10)
GROUP BY G.groupCode


In this instance it does not show the group if the count associated is 0. I want this to show every element from musicalgroup even if the count is 0.
When I do this:

SELECT G.groupCode, G.groupName, COUNT(C.cdCode) AS numberOfTops10CDs FROM musicalgroup G
LEFT OUTER JOIN cd C ON C.groupCode = G.groupCode
GROUP BY G.groupCode


It shows even if the count is 0, but I need to only show CDs with a rating under 10. How would I accomplish both of those goals in one query?

Answer

Try this:

SELECT G.groupCode,
       G.groupName,
       COUNT(C.cdCode) AS numberOfTops10CDs 
FROM musicalgroup G 
LEFT OUTER JOIN cd C
    ON C.groupCode = G.groupCode AND
       C.cdCode in (SELECT distinct cdCode FROM topcds WHERE rating <= 10)
GROUP BY G.groupCode