Left join that is not showing all left table values

Question

A LEFT JOIN should show all rows on the left table however when running the following query, I am only getting place values where there is a count of 1 or more. Please can someone give me some guidance as to where I might be going wrong:

SELECT places.placeId,
       placeName,
       COUNT(orderId) AS orderCount
FROM   places
       LEFT JOIN orders
         ON places.placeId = orders.placeId
WHERE  places.companyId = 1
       AND stateId = 1
       AND orderstateId = 1
       AND orderName NOT LIKE 'Delivery%'
GROUP  BY places.placeId,
          places.placeName
ORDER  BY orderCount DESC,
          placeName ASC 

Thanks in advance

Answer 1

Your WHERE condition converts the OUTER JOIN back to an INNER JOIN.

The non matched rows will have NULL for all the orders columns and be eliminated by the WHERE clause. Try this.

SELECT places.placeId,
       placeName,
       COUNT(orderId) AS orderCount
FROM   places
       LEFT JOIN orders
         ON places.placeId = orders.placeId
       AND orders.stateId = 1
       AND orders.orderstateId = 1
       AND orders.orderName NOT LIKE 'Delivery%'
WHERE  places.companyId = 1
GROUP  BY places.placeId,
          places.placeName
ORDER  BY orderCount DESC,
          placeName ASC 

Answer 2

If these columns stateID, orderstateID, and OrderName came from ORDERS table then you will have a problem then. As you can see, If places.placeID is not present on orders table, the following columns are nulls for the orders table. And that's the reason why some records won't show because of the condition you have provided on orders table.