右联接查询不起作用

Question

我有两个表：opensalesorder和items。

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

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和items中找不到与ItemName匹配的内容。

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

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

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

查询正确吗？

谢谢

Answer 1

这是您的查询：

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;

该where条件opensalesorder是“撤销”的right join。该值NULL将导致它失败。

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

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;