右联接查询不起作用 [英] Right join query not working

问题描述

我有两个表:opensalesorder和items.

我想基于item_number从两个表中检索数据，并且在以下查询中工作正常. (使用内联接)

I want to retrieve data from both tables based on the item_number and it's working fine with the below query. ( USING INNER JOIN )

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` 
INNER JOIN items on opensalesorder.item_number = items.ItemName 
WHERE items.ItemType = 'Stock' and opensalesorder.status NOT LIKE 'on po' 
GROUP BY opensalesorder.item_number

但是我也希望'items'表中的所有行，即使在opensalesorder和商品中找不到与ItemName匹配的地方.

But I also want all rows from the 'items' table even if there isn't any match found for ItemName from opensalesorder and items.

但是使用下面的查询似乎对我不起作用.

But using below query seems not to be working for me.

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` 
RIGHT JOIN items on opensalesorder.item_number = items.ItemName  
WHERE items.ItemType = 'Stock' and opensalesorder.status NOT LIKE 'on po' 
GROUP BY opensalesorder.item_number

即使在左侧找不到匹配项，右连接也会从右表返回结果.

The right join will return a result from the right table even if no match found on left side.

查询对吗?

谢谢

推荐答案

这是您的查询:

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` right join
     items
     on opensalesorder.item_number = items.ItemName  
 WHERE items.ItemType = 'Stock' and opensalesorder.status NOT LIKE 'on po'
 group by opensalesorder.item_number;

opensalesorder上的where条件是撤消" right join.值NULL会导致它失败.

The where condition on opensalesorder is "undoing" the right join. The value NULL will cause it to fail.

解决方案是将其移至on子句:

The solution is to move it to the on clause:

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` right join
     items
     on opensalesorder.item_number = items.ItemName and
        opensalesorder.status NOT LIKE 'on po'
 WHERE items.ItemType = 'Stock'
 group by opensalesorder.item_number;

这篇关于右联接查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！