Re: Combining two queries

On Thu, Dec 18, 2014 at 1:57 PM, Robert DiFalco
<robert.difalco@gmail.com> wrote:
> Thanks! So how would I combine them so that I would get a single row with
> the mutual friend count and isFriends for a given pair of users? I can't
> figure out how to modify what you've posted so that it gives the results
> like the compound query I quoted in my original post.
>
> On Thu, Dec 18, 2014 at 1:31 PM, Patrick Krecker <patrick@judicata.com>
> wrote:
>>
>> On Thu, Dec 18, 2014 at 12:10 PM, Robert DiFalco
>> <robert.difalco@gmail.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.

(adding back psql-general)

Well it would not be a single SELECT statement anymore :)

There are probably other ways of doing this, I just came up with this
one off the top of my head:

SELECT is_user, c FROM (
    SELECT friend_id = USER1 AS is_user, count(friend_id) OVER
(partition BY friend_id = USER1) AS c FROM (
        SELECT friend_id FROM friends WHERE user_id IN (USER1, USER2)
OR (user_id = USER1 AND friend_id = USER2) GROUP BY friend_id HAVING
(count(friend_id) > USER1 OR friend_id = USER1)
        ) AS t1)
    AS t2 GROUP BY is_user, c;

It should return 2 rows, one with is_user = t and one with is_user =
f. is_user = t will be present if the two users are friends, and will
always have c = 1. is_user = f will be present if there are mutual
friends, and c will be the number of mutual friends.