From: | Patrick Krecker <patrick(at)judicata(dot)com> |
---|---|
To: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Combining two queries |
Date: | 2014-12-18 21:31:23 |
Message-ID: | CAK2mJFNDbift-WPMbF5Uw-V2k-vT25xOfxjY96w6Veg6Cbt0NQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Dec 18, 2014 at 12:10 PM, Robert DiFalco
<robert(dot)difalco(at)gmail(dot)com> wrote:
> 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.
Assuming the friendships are not repeated (that is, if 1 is friends
with 3, then the tuple (1, 3) appears only once), you can find just
the mutual friends by using this one:
(This would be for users 1 and 2):
SELECT friend_id FROM friends WHERE user_id IN (1, 2) GROUP BY
friend_id HAVING count(friend_id) > 1;
You can additionally test if 1 and 2 are friends by doing:
SELECT friend_id FROM friends WHERE user_id IN (1, 2) OR (user_id = 1
AND friend_id = 2) GROUP BY friend_id HAVING (count(friend_id) > 1 OR
friend_id = 1);
If 1 appears in the list, then 1 and 2 are friends. Any other rows are
the mutual friends.
From | Date | Subject | |
---|---|---|---|
Next Message | John McKown | 2014-12-18 21:46:02 | Re: Combining two queries |
Previous Message | David G Johnston | 2014-12-18 21:29:52 | Re: Combining two queries |