Re: Combining two queries

>>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