From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Combining two queries |
Date: | 2014-12-19 11:07:00 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D8828B26B87@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>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 additional 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
regards,
Marc Mamin
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Mamin | 2014-12-19 11:27:55 | Re: Combining two queries |
Previous Message | Ramesh T | 2014-12-19 09:17:44 | Re: pg_dump |