I have an table of people where each person can have a associate partner like this:
| id_person | Name | id_partner |
|---|---|---|
| 1 | Javi | 5 |
| 2 | John | 4 |
| 3 | Mike | 6 |
| 4 | Lucy | 2 |
| 5 | Jenny | 1 |
| 6 | Cindy | 3 |
So I would like to have a query where I can get all the couples without repetance like
| Name 1 | Name 2 |
|---|---|
| Javi | Jenny |
| John | Lucy |
| Mike | Cindy |
I now how I would do it in python but in sql I have no clue
Thank you in advance!!!
In SQL we solve these problems using a self inner join which is when we create a temporary alias of the table and join the table to its alias. The join condition would be two different columns, one from each table being equal to each other.
This allows you to make pairs of columns from single columns, but all of the rows will be duplicated in a backwards representation. To get rid of those, you need a filter where you compare one column to be less than the corresponding column in the other table.
As the condition implies, it means one set of people's IDs must all be less than the IDs of all of the partners.
This is how you'd write the query for your table:
SELECT Table1.Name AS "Name 1", Table2.Name AS "Name 2" FROM Table1 INNER JOIN (Table1) Table2 ON Table1.id_person = Table2.id_partner WHERE Table1.id_person < Table2.id_person
And then if you want you can order by either of the Name columns to sort the output.
SELECT t2.[Name] AS Name1, t1.[Name] AS Name2 from Table_Name t1 INNER JOIN Table_Name t2 ON t2.id_partner = t1.id_person