CODEWITHSUNDEEP
sqlpostgresql
user81434
user81434

Reputation:

sql query to fetch alternate columns

I have a connection table in PostgreSQL where each row is a pair of users who are connected:

connection:
  id
  firstuserid
  seconduserid
  ...

For a given user id, I want to fetch the other user id for all of their connections. This is obvious if I do two queries:

select firstuserid from connection where seconduserid = 123
select seconduserid from connection where firstuserid = 123

Is there a way I can do this in one query, returning just the other user id rather than both?

Upvotes: 0

Views: 70

Answers (1)

Radim Bača
Radim Bača

Reputation: 10711

Simply use UNION

select firstuserid from connection where seconduserid = 123
union
select seconduserid from connection where firstuserid = 123

without UNION you may write

select case when firstuserid = 123 then seconduserid 
                                   else firstuserid end
from connection 
where seconduserid = 123 or firstuserid = 123

Upvotes: 1

Related Questions