|
>>I have a table called friends with a user_id and a friend_id (both of these relate to an id in a users table). >>For each friend relationship there are two rows. There are currently ONLY reciprocal relationships. So if user ids 1 and 2 are friends there will be two rows (1,2) and (2,1). >>For 2 arbitrary ids, I need a query to get two pieced of data: >> * Are the two users friends? >> * How many friends do the two users have in common. >>Is there a way to do this with one query? Currently I've only been able to figure out how to do it with two. >>SELECT >> EXISTS( >> SELECT 1 >> FROM friends >> WHERE user_id = 166324 AND friend_id = 166325) AS friends, >> (SELECT COUNT(1) >> FROM friends f1 JOIN friends f2 ON f1.friend_id = f2.friend_id >> WHERE f1.user_id = 166324 AND f2.user_id = 166325) AS mutual; >>I'm wondering if there is a better way to do this using only one query. I've tried a couple of GROUP BY approaches but they haven't worked. >> > >Hi, >this should do the job, but requires an aditional check constraint user_id <> friend_id to be on the safe side: > > >SELECT count(case when c1=1 then true end)=1 as are_friend, > count(*)-1 as common_friends >FROM > ( > SELECT count(*) as c1 > FROM friends > WHERE user_id IN (USER1, USER2) > GROUP BY case when user_id = USER2 then USER1 else USER1 end, > friend_id > HAVING COUNT (*) =2 > OR COUNT(case when friend_id =USER1 then true end)=1 > ) q1 > fix: SELECT count(case when c1=1 then true end)=1 as are_friend, count(case when c1=2 then true end) as common_friends FROM ( SELECT count(*) as c1 FROM friends WHERE user_id IN (USER1, USER2) GROUP BY case when user_id = USER2 then USER1 else USER1 end, friend_id HAVING COUNT (*) =2 OR COUNT(case when friend_id =USER1 then true end)=1 ) q1 regards, Marc Mamin |
