MySQL 联合和连接

Question

我有下表

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

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

例如示例表

采购订单

  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

编辑:这是我必须要做的，但问题是规定 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

@strawberry 给了我答案

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