mysql - 右侧不存在LEFT JOIN表和空值

我有两个表，Customers和customerTowns。我想将城镇名称存储在customerTowns中，并从townID中存储的Customers链接到它

|Customers| |townID |

|customerTowns| |townID | |townName |

我的sql如下

SELECT townName, Customers.townID
FROM customerTowns
LEFT OUTER JOIN Customers
ON Customers.townID = customerTowns.townID

这几乎返回了我正在寻找的结果。除此之外，我希望对townID中的每个条目重复customerTowns。

|townName |townID| |London |1 | |London |1 | |London |1 | |London |1 | |London |1 | |Manchester|NULL | |Liverpool |NULL |

我觉得我必须亲近，只是想不出如何只返回一行，或者为什么要返回多行！

我想要的输出是；

|townName |townID| |London |1 | |Manchester|NULL | |Liverpool |NULL |

最佳答案

使用分组依据或不同。

SELECT townName, Customers.townID
FROM customerTowns
LEFT OUTER JOIN Customers
ON Customers.townID = customerTowns.townID
GROUP BY townName, Customers.townID;

SELECT DISTINCT townName, Customers.townID
FROM customerTowns
LEFT OUTER JOIN Customers
ON Customers.townID = customerTowns.townID;

关于mysql - 右侧不存在LEFT JOIN表和空值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28996886/