CODEWITHSUNDEEP
sql
squareOne
squareOne

Reputation: 139

Select two records where a two way relationship exists

I have a User table that contains User ID and Name.

I have a Likes table that contains UserID1 and UserID2.

When User1 likes User2, their IDs are entered in to the likes table respectively.

How can I retrieve all users that BOTH like each other?

Upvotes: 2

Views: 64

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

Use self join

select t1.* from Likes as t1 
inner join Likes as t2  on t1.UserID1 = t2.UserID2 and t1.UserID2 = t2.UserID1

Upvotes: 0

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Join the likes table with itself and select the rows with mutual likes:

    select * 
      from Likes like1 
inner join Likes like2 on like1.UserID1 = like2.UserID2 
                      and like1.UserID2 = like2.UserID1

Upvotes: 2

Related Questions