Select one of two columns based on their value in SQL

Question

I have a scenario where I have a users table where users are connections to each other based on the connections table. The connections table has a user_id1 field and a user_id2 field.

I want to get the connections for a specific user:

select id, user_id1, user_id2 
from connections 
where user_id1 = 1 or user_id2 = 1

But the user_id's of the connections are separated into two columns. How can I get the connections of the specific user in one one column.

I've tried this but it didn't work:

select user_id1 or user_id2 
from 
    (select id, user_id1, user_id2 
     from connections 
     where user_id1 = 1 or user_id2 = 1) as con 
where user_id1 != 1 and user_id2 != 1

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2021-04-04 11:39:58 +00:00
Commented Apr 4, 2021 at 11:39 — Gordon Linoff
– Gordon Linoff, Commented Apr 4, 2021 at 11:39

forpas · Accepted Answer · 2021-04-04 08:29:56Z

3

Use a CASE expression:

SELECT CASE user_id1
         WHEN 1 then user_id2 
         ELSE user_id1
       END AS user_id
FROM connections 
WHERE 1 IN (user_id1, user_id2)

answered Apr 4, 2021 at 8:29

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select one of two columns based on their value in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related