1

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.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
Jaime Duarte
  • 25
  • 1
  • 1
  • 3
  • Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. An inner join on or where that requires a right table column to be not null after a left join on removes any rows extended by nulls, ie leaves only inner join on rows, ie "turns outer join into inner join". You have that. – philipxy Mar 27 '19 at 17:31
  • Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Mar 27 '19 at 17:33
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] – philipxy Mar 27 '19 at 17:34

1 Answers1

21

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
jarlh
  • 42,561
  • 8
  • 45
  • 63