CODEWITHSUNDEEP
mysqlsql
Alan Mark Kristensen
Alan Mark Kristensen

Reputation: 129

Return distinct data pairs with duplicate data

I would like to write an SQL query that returns a list of pairs of persons sharing the same city , without duplicates. (This is just test data, as the real database is very random and would be confusing for the question). Sample data:

Person | City
Tom    | New York
Kenny  | Washington
Katja  | New York
Kerry  | New York
Dan    | Washington

Sample return:
Tom, Katja
Kenny, Dan
Kerry,

My intuition was the following:

SELECT DISTINCT a.person, b.person
FROM People AS a, People AS b
WHERE a.city = b.city;

However this returns duplicates in the b column, which is not ideal. I've tried a few tweaks but i cannot get it to return distinct values.

Please explain your answer and thank you!

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you want pairs, you can do:

SELECT p1.person, p2.person
FROM People p1 LEFT JOIN
     People p2
     ON p1.city = p2.city AND p1.person <> p2.person
WHERE p2.person IS NULL OR p1.person < p2.person;

The LEFT JOIN ensures that all people are included, even if there are no matches.

Here is a SQL Fiddle.

You might find it more convenient to generate a list for each city:

select p.city, group_concat(p.person order by p.person)
from people p
group by p.city;

Upvotes: 1

Related Questions