CODEWITHSUNDEEP
sqljoinleft-join
Michael Wilson
Michael Wilson

Reputation: 251

Left join that is not showing all left table values

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

Upvotes: 14

Views: 29512

Answers (4)

Martin Smith
Martin Smith

Reputation: 453908

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

Upvotes: 34

dan1111
dan1111

Reputation: 6566

Even when you use a LEFT JOIN, all rows have to meet the conditions in the WHERE clause. In this case, the WHERE clause has requirements related to orders, thus you won't return any rows where orders doesn't match to places.

A simple fix would be to allow null values to satisfy your WHERE clause:

orderstateId = 1

changes to

(orderstateId is NULL or orderstateId = 1)

And likewise for the other variables.

This would probably do what you want, but you have to check if rows with null values in the orders table could then be included and mess up your result. It might be better to redesign your query in another way.

Upvotes: 1

John Woo
John Woo

Reputation: 263893

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.

Upvotes: 1

usr
usr

Reputation: 171246

You are saying orderstateId = 1. I guess this binds to orders.orderstateId = 1. This conditions fails for a null orderstateId which happens when left-joining without match.

Change it to orders.orderstateId = 1 OR orders.orderstateId IS NULL

Upvotes: 0

Related Questions