LEFT JOIN查询未返回第一个表中的所有行 [英] LEFT JOIN query not returning all rows in first table

问题描述

使用Management Studio for SQL Server 2008 R2.

Using management studio for SQL Server 2008 R2.

尝试执行LEFT JOIN，我需要从第一个表返回所有行，无论该行是否可以与第二个表绑定. 不确定我是否正确解释了.

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.

这就是我现在得到的:

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

我需要查询以退回公司出售的所有产品，并显示其在商店001中的库存.

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

但是现在，它只会显示在商店001中提到产品库存的行.

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

因此，基本上，如果未提及001商店，我需要查询以返回0库存.

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

所有仅在商店002中有库存的产品也需要列出，并且其库存中用0.

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

推荐答案

将b条件从WHERE移到ON以获得真实的LEFT JOIN. (使用WHERE子句中的b条件，它作为常规inner join ...执行)

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

这篇关于LEFT JOIN查询未返回第一个表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！