我有一个带有ID
字段(自动编号)和item
字段的自定义表。我公司提供的另一个表有很多字段,但它也有一个item
、whse
和qty_on_hand
字段。
我只想查找自定义表中列出的项,所以我使用了RIGHT JOIN但是,我还需要根据一些标准进行筛选。我需要"whse=A"one_answers"qty_on_hand>0",但当我这样做时,它会排除不匹配的项目,而不是只返回NULL
。如何才能返回自定义表中的所有行,如果没有匹配项,只返回NULL
?
我主要在Access中使用设计视图,但这是我在设计视图中创建的SQL:
SELECT
customtable.ID
,customtable.item
,Sum(companytable.qty_on_hand) AS SumOfqty_on_hand
,companytable.whse
FROM companytable
RIGHT JOIN customtable ON companytable.item = customtable.item
GROUP BY
customtable.ID
,customtable.item
,companytable.whse
HAVING
(((Sum(companytable.qty_on_hand))>0)
AND ((companytable.whse)="A"))
ORDER BY
customtable.ID;
您可以将其切换到LEFT JOIN
,以便对ON
子句中的companytable
应用一些筛选,这将导致companytable
中的那些记录在加入之前被丢弃:
SELECT customtable.ID,
customtable.item,
Sum(companytable.qty_on_hand) AS SumOfqty_on_hand,
companytable.whse
FROM customtable
LEFT JOIN companytable ON
companytable.item = customtable.item AND
companyTable.qty_on_hand > 0 AND
companyTable.whse = "A"
GROUP BY customtable.ID,
customtable.item,
companytable.whse
ORDER BY customtable.ID;
我不确定Access在其"设计"视图中会如何表示这一点,但它应该可以正常工作。
或者,您可以使用一个子查询,在加入之前过滤companytable
:
SELECT customtable.ID,
customtable.item,
Sum(comptable.qty_on_hand) AS SumOfqty_on_hand,
comptable.whse
FROM (SELECT * FROM companytable WHERE qty_on_hand > 0 AND whse = "A") AS comptable
RIGHT JOIN customtable ON comptable.item = customtable.item
GROUP BY customtable.ID,
customtable.item,
comptable.whse
ORDER BY customtable.ID;