mysql - 从两个表中选择 WHERE only 1 unique result from table_1

Question

我一直在搜索，似乎无法弄清楚如何获得以下内容，任何帮助非常不胜感激!

给定这些表:

表_1

+-----------+----------+-------+
| firstName | lastName |  zip  |
+-----------+----------+-------+
| John      | Doe      | 80210 |
| Barty     | Crouch   | 80598 |
| Mary      | Jane     | 80233 |
| John      | Doe      | 80210 |
+-----------+----------+-------+

表_2

+-----------+----------+-------+
| firstName | lastName |  zip  |
+-----------+----------+-------+
| John      | Doe      | 80210 |
| Barty     | Crouch   | 80598 |
| Max       | Payne    | 80233 |
+-----------+----------+-------+

如果 Table_1 中的记录只有一个结果，我该如何选择表之间的匹配记录？

类似这样的事情(作为查询应该做什么的示例写出的结尾部分是我无法弄清楚的)

SELECT Table_1.firstName,
   Table_1.lastName,
   Table_1.zip,
   Table_2.firstName,
   Table_2.lastName,
    Table_2.zip
FROM Table_1, Table_2
WHERE Table_1.firstName = Table_2.firstName and Table_1.lastName = Table_2.lastName and Table_1.zip = Table_2.zip
and ONLY ONE RESULT First,Last,zip FROM TABLE_1

这只会返回 Barty Crouch 80598

Answer 1

加入一个派生表，该表是来自 table_1 的行的分组列表，只有 1 个组计数。:

SELECT T2.FirstName, T2.LastName, T2.Zip
FROM TABLE_2 AS T2
JOIN (
SELECT T1.FirstName, T1.LastName, T1.Zip
FROM TABLE_1 AS T1
GROUP BY T1.FirstName, T1.LastName, T1.Zip
HAVING COUNT(*) = 1) AS T1
  ON T1.FirstName = T2.FirstName
 AND T1.LastName = T2.LastName
 AND T1.Zip = T2.Zip