mysql - Finding Mutual Friends list -

mysql - Finding Mutual Friends list -

here schema of db:

id | user1 | user2 | status //status 1 means user friend 1 1 2 1 2 3 1 1 3 1 4 1 4 2 3 1 5 4 2 1

in above example

userid 1 has 3 friends 2,3,4 userid 2 has 3 friends ie. 3,4,1 userid 3 has 2 friends ie: 1,2 userid 4 has 2 friends ie:1,2

i need find mutual friends between 2 people.

any ideas,

i not sure if best solution. maybe this:

select * ( select case when t2.user1=1 t2.user2 else t2.user1 end userid t t2 1 in (t2.user1,t2.user2) , t2.[status]=1 ) tbl exists ( select null t 2 in (t.user1,t.user2) , tbl.userid=(case when t.user1=2 t.user2 else t.user1 end) , t.[status]=1 )

this gets mutual friends user 1 , 2. 3,4

mysql doctrine2