mysql - MySQL Union和JOIN

我有下表

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

如何返回所有链接到未删除的，非空的purchase_order的项目以及所有没有链接到它们的项目的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

编辑：这是我必须去的地方，但问题是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

最佳答案

@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

关于mysql - MySQL Union和JOIN，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40491822/