From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Combining two queries |
Date: | 2014-12-18 20:10:12 |
Message-ID: | CAAXGW-wuoNB=e6=6FWDpDbMr=fxCXsUsisxjNWA1swkdT+AmQQ@mail.gmail.com |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-12-18 21:29:52 | Re: Combining two queries |
Previous Message | Merlin Moncure | 2014-12-18 19:59:47 | Re: pl/pgsql trigger function - compare *most* columns in NEW vs. OLD |