sql - LEFT JOIN query not returning all rows in first table

Question

Using management studio for SQL Server 2008 R2.

Trying to do a LEFT JOIN and I need to return all rows from the first table regardless of the row being able to tie with the second table. Not sure I explained that right.

This is what I got right now:

select a.id, a.name, b.store, b.stock
from products a left join stock b
on a.id = b.id
where b.store = '001'
order by a.id

I need the query to return all products that the company sells and also show it's stock in store 001.

But the way it is right now it will only show the rows that have mention of the product stock in store 001.

So, basically, I need the query to return 0 stock if the 001 store is not mentioned.

All products that only have stock in store 002 need to be listed as well, with a 0 as it's stock.

Answer 1

Move the b condition from WHERE to ON to get a real LEFT JOIN. (With the b condition in the WHERE clause, it executes as a regular inner join...)

select a.id, a.name, b.store, b.stock
from products a left join stock b
  on a.id = b.id and b.store = '001'
order by a.id