CODEWITHSUNDEEP
sql
cp151
cp151

Reputation: 197

Postgresql - How to select results depending on results of another query

I'm working on a server where people can easily add, remove friends, and ask for their current ips if they are logged in.

I structured my database to have a first table containing users name, another one containing addresses + a fk on those users and finally another one called 'friends' that contains user -> friend.

For Example :

A, B, C are users, A is friended with C and the other way, B is friended with C but C is not friended with B.

Friend table

user friend
 A      C
 C      A
 B      C

B wants friends ips. How to i write an sql query that would be able to check if C is also friended with B ?

Upvotes: 0

Views: 129

Answers (1)

roman
roman

Reputation: 117380

If I understand your problem, you have to check is your friends mututal or not. It could be performed with left outer join

select
    t1.*,
    case when t2.user is not null then 1 else 0 end is_mutual
from test as t1
    left outer join test as t2 on t2.user = t1.friend and t2.friend = t1.user

SQL FIDDLE EXAMPLE

Upvotes: 1

Related Questions