MySQL联合和连接

Question

我有以下表格

purchase_order         item
------------------     ------------
id (PK)                id (PK)
deleted                deleted
name                   name
                       purchase_order_id (FK)

如何返回链接到未删除的非空purchase_order的所有项的列表，以及没有链接到它们的项的所有purchase_orders的列表(无论是否已删除)。

例如，示例表格

purchase_order

  id    name         deleted
  ---------------------------
  1     Big Sale     0
  2     Other Sale   1
  3     Empty Sale   0

项目

  id    name         deleted  purchase_order_id
  ----------------------------------------------
  1     Fruit        1        1
  2     Bread        0        1
  3     Water        0        2

正确的查询给出了如下结果：

po_id name          item_id  name
------------------------------------
1      Big Sale     2        Bread
3      Other Sale   NULL     NULL

Edit:这就是我要去的地方，但问题是规定i.deleted = 0，它会阻止任何没有连接到项目上的行返回

SELECT po.id, po.name, i.id, i.name
FROM purchase_order po
LEFT JOIN item i ON i.purchase_order_id=po.id
WHERE po.deleted = 0 AND i.deleted = 0

Answer 1

@草莓给了我答案

SELECT po.id, po.name, i.id, i.name
FROM purchase_order po
LEFT JOIN item i ON i.purchase_order_id=po.id AND i.deleted = 0
WHERE po.deleted = 0