Query With Left Join Not Returning Rows For Count of 0

Using LEFT JOIN to get a count returns a zero count when no data in main table

This is a query that is ALWAYS going to return a value because count(*) will always return something. If you don't want to see that, add a case statement to trap 0 and return null.

How to Query for COUNT on a join table and return records with BOTH count=0 AND count 0

just remove the where condition from your 2nd query

SELECT p.prodid, p.prodname, avg(r.stars), count(r.stars)
FROM products p
LEFT OUTER JOIN reviews r 
ON p.productid=r.productid
GROUP BY p.productid,p.prodname

Related Topics