MySQL get pairs of rows from same set of foreign table

Question

My issue is the next one.

I have 3 tables: People, Cars and Driven:

People:

Id     Name
1   |   Tom
2   |   James
3   |   Charles
4   |   Eric
5   |   Thomas
6   |   Robert
7   |   Kim
8   |   Ellias

Cars:

Id      Name
1   |   Ford
2   |   Nissan
3   |   Hyundai

Driven:

PID    CID (People ID & Car ID)
1    |   1
2    |   1
5    |   1
5    |   2
6    |   1
6    |   2
7    |   1
7    |   2
7    |   3
8    |   1

I Want to retrieve pairs of people that driven the SAME SET OF CARS. I mean: if Tom driven only Ford and James driven also ONLY Ford, i want to return this pair Tom/James as result. Also i want to include pairs of people that didn't driven any car (ie. Charles/Eric (0 cars driven both)).

The query result with the example above should return two columns per result, for example:

Name       Name     
Tom     |  James     (Only Ford)
Tom     |  Ellias    (Only Ford)
James   |  Ellias    (Only Ford)
Charles |  Eric      (None BOTH)
Thomas  |  Robert    (Ford and Nissan BOTH)

Also notice that Kim has driven Ford, Nissan and Hyundai. So Kim is not going to be pair with anybody. Tom James and Ellias all are driven Ford, so they are pair with themselves.

I'm tried with cartesian product and relational division, but I didn't find a solution. If someone can help me at least with a tip i will be really grateful. Thanks!

Answer 1

You can use the following query:

SELECT p.Id, p.Name, 
          COALESCE(GROUP_CONCAT(c.Name ORDER BY c.Name), 'None') AS cars_driven
FROM People AS p 
LEFT JOIN Driven AS d ON p.Id = d.PID
LEFT JOIN Cars AS c ON c.Id = d.CID
GROUP BY p.Id, p.Name;

to get the list of cars driven per person.

Output:

Id Name    cars_driven
-----------------------
1  Tom     Ford
2  James   Ford
3  Charles None
4  Eric    None
5  Thomas  Ford,Nissan
6  Robert  Ford,Nissan
7  Kim     Ford,Hyundai,Nissan
8  Ellias  Ford

Using the above query twice as a derived table you can get the required result:

SELECT t1.Name, t2.Name, t1.cars_driven
FROM ( 
   SELECT p.Id, p.Name, 
          COALESCE(GROUP_CONCAT(c.Name ORDER BY c.Name), 'None') AS cars_driven
   FROM People AS p 
   LEFT JOIN Driven AS d ON p.Id = d.PID
   LEFT JOIN Cars AS c ON c.Id = d.CID
   GROUP BY p.Id, p.Name) AS t1
JOIN (   
   SELECT p.Id, p.Name, 
          COALESCE(GROUP_CONCAT(c.Name ORDER BY c.Name), 'None') AS cars_driven
   FROM People AS p 
   LEFT JOIN Driven AS d ON p.Id = d.PID
   LEFT JOIN Cars AS c ON c.Id = d.CID
   GROUP BY p.Id, p.Name
) AS t2 ON t1.Id < t2.Id AND t1.cars_driven = t2.cars_driven;

Output:

Name    Name    cars_driven
----------------------------
Tom     James    Ford
Charles Eric     None
Thomas  Robert   Ford,Nissan
Tom     Ellias   Ford
James   Ellias   Ford

Demo here