On Thu, Dec 18, 2014 at 12:10 PM, Robert DiFalco <robert.difalco@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
