0

I've a table,

CREATE TABLE friends (
    friend1 serial references users(id),
    friend2 serial references users(id),
    unique (friend1, friend2)
);

Friends are in no particular order, but I need to have a query that returns friends where it is a one-way friendship. For example, if the data is:

friend1 | friend2
-----------------
1       | 2
2       | 1
3       | 2

the query for friend id=2 should return:

friend1 | friend2
-----------------
3       | 2

because there is not another row where friend1=2 and friend2=3.

  • unique (friend1, friend2) should probably be PRIMARY KEY (friend1, friend2) – Jasen Jan 22 at 2:25
  • @Jasen you are right, thanks – Dah Person Jan 29 at 22:35
1

You can use EXCEPT to remove the complementing rows.

SELECT friend1,friend2 FROM friends 
    WHERE friend1=2 or friend2=2
EXCEPT
SELECT friend2,friend1 FROM friends
    WHERE friend1=2 or friend2=2;

But maybe using a CTE will perform better.

WITH buddies 
AS ( SELECT friend1,friend2 FROM friends 
        WHERE friend1=2 or friend2=2 )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies

Unfolding the or into a union may help performance too.

WITH buddies 
AS ( SELECT friend1,friend2 FROM friends 
        WHERE friend1=2
     UNION ALL
     SELECT friend1,friend2 FROM friends 
        WHERE friend2=2
   )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.