Transform MySQL Table into Edge List

Question

I have an MySQL table that looks like this:

Project      User
ABC      1
ABC      2
ABC      3
DEF      2
DEF      4

In other words, you can see from the input that persons 1, 2 and 3 worked on project ABC. In my data, that means that they have a relationship (i.e. they worked on the same project).

I am trying to find a way so that I can only have the links between users. Users have a link if they worked on a project together. The final list would look like this:

1 2
1 3
2 3
2 4

Thus, I would like to have one line per relationship.

How would you advise me to do?

Answer 1

Use self join

select t1.user user1, t2.user user2
from table1 t1 inner join table1 t2 on t1.user<t2.user and
t1.project=t2.project

Fiddle