我有两个要合并的表。我希望左边的表显示所有EmbroideryID,而右边的表应该返回那些EmbroideryID的null。但是,我只得到两个表中都存在的EmbroideryID,这不是我想要的。我希望我的左表中的所有结果,无论我的右表中是否有结果。
左表:mysql> SELECT * FROM Embroidery WHERE Customer = 'AAAAAAAA';+--------------+----------+--------------------------+| EmbroideryID | Customer | Description |+--------------+----------+--------------------------+| 1 | AAAAAAAA | Embroidery1 || 2 | AAAAAAAA | Embroidery2 || 3 | AAAAAAAA | Embroidery3 || 4 | AAAAAAAA | Embroidery4 || 5 | AAAAAAAA | Embroidery5 |+--------------+----------+--------------------------+
右表:SELECT * FROM EmbroideryForMen;+----------+--------------+-----------+| Customer | EmbroideryID | ManNumber |+----------+--------------+-----------+| AAAAAAAA | 1 | 4 || AAAAAAAA | 1 | 8 || AAAAAAAA | 1 | 12 |+----------+--------------+-----------+
我期望的是:+--------------+----------+--------------------------+-----------+| EmbroideryID | Customer | Description | ManNumber |+--------------+----------+--------------------------+-----------+| 1 | AAAAAAAA | Embroidery1 | 4 || 1 | AAAAAAAA | Embroidery2 | 8 || 1 | AAAAAAAA | Embroidery3 | 12 || 2 | AAAAAAAA | Embroidery4 | NULL || 3 | AAAAAAAA | Embroidery5 | NULL || 4 | AAAAAAAA | Embroidery5 | NULL || 5 | AAAAAAAA | Embroidery5 | NULL |+--------------+----------+--------------------------+-----------+
这也是您可以查看的SQLFiddle:http://sqlfiddle.com/#!2/579bac/3
我想要看到这样的表的原因是因为并非总是用户会选择一个人接受刺绣,所以永远不会创建行/记录。我想避免将空记录写入EmbroideryForMen,但如果需要,我会做。
任何帮助是极大的赞赏。
最佳答案
这是您可以实现的方法:
将m.customer = "AAAAAAAA"
替换为e.customer = "AAAAAAAA"
(因为m.customer可以为null且null!=“ AAAAAAAA”),并且
删除AND e.EmbroideryID = 1
(以显示所有行)
SELECT *
FROM Embroidery e
LEFT JOIN EmbroideryForMen m
ON e.EmbroideryID = m.EmbroideryID
AND e.Customer = m.Customer
WHERE e.Customer = "AAAAAAAA";