我有两个相关的SQL表:
购买
id (PK),
code,
status (complete, void)
接收
id (PK),
code,
purchase_id (FK),
status (complete, void)
具有Purchase
具有多个Receiving
(一对多(
如果LEFT JOIN的结果是
----------------------------------------------------------------------------------------
|Purchase.id|Purchase.code|Purchase.status|Receiving.id|Receiving.code|Receiving.status|
----------------------------------------------------------------------------------------
| 1 | PO001 | complete | 1 | RG001 | complete | X HIDE
| 2 | PO002 | complete | 2 | RG002 | void | X HIDE
| 2 | PO002 | complete | 3 | RG003 | complete | X HIDE
| 2 | PO003 | complete | NULL | NULL | NULL | V SHOW
| 3 | PO004 | complete | 4 | RG004 | void | V SHOW
重点是,我想显示"购买",只有在"接收"状态不完整且为空的情况下
因此,结果应该只显示PO003和PO004。
PO002不应显示,因为它已经有完整的"接收"而PO001也不会显示,因为它已经有1个完整的"接收"。
我希望你们明白我的意思。如何在SQL上查询它?
在查询中放入WHERE
条件。
SELECT p.id, p.code, p.status, r.id, r.code, r.status
FROM Purchase p
INNER JOIN Receiving r ON p.id = r.purchase_id
WHERE r.id IS NULL
AND r.status != 'complete'
试试这个;
select *
from Purchase p
join Receiving R
on p.id = r.purchase_id
where
r.id not in (
select id
from Receiving
where
status = 'complete' or
status is not null
)