Select one of two columns based on their value in SQL

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

Answer

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)