CODEWITHSUNDEEP
mysqlsql
Dillon Doyle
Dillon Doyle

Reputation: 315

Select from two tables WHERE only 1 unique result from table_1

I've been searching and can't seem to figure out how to get the following, any help greatly appreciated!

Given these tables:

Table_1

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

Table_2

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

How would I select matching records between tables, where there is only one result for that record in Table_1?

Something like this (the end part written out as example of what query should do is what I can't figure out)

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

Which would only return Barty Crouch 80598

Upvotes: 2

Views: 48

Answers (2)

Iłya Bursov
Iłya Bursov

Reputation: 24156

one of many possible variants:

SELECT Table_1.firstName,
   Table_1.lastName,
   Table_1.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
group by table_1.firstname, table_1.lastname, table_1.zip
having count(*) = 1

Upvotes: 1

T McKeown
T McKeown

Reputation: 12857

Join to a derived table that is a grouped list of rows from table_1 having only 1 group count.:

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

Upvotes: 3

Related Questions