Mike Pala Mike Pala - 2 months ago 6
SQL Question

SQL is a value in a column a part of a list in another table?

I believe this question is very simple but in it's so simple I'm not sure how to approach it. I searched for the answer but the closest I got was something like this:

SELECT * FROM USERS WHERE user IN ('user1', 'user2', 'user3')

... witch is not exactly what I need. I guess I don't know how to formulate the question for google. I have a table where ALL users are listed and I have a 2nd table where only users who are admins are listed. I need to run a:

Select *

on the first table and I guess JOIN it with the 2nd table so that I get one additional column with the value "yes" or "no" depending on if the user exists in the 2nd table.

Answer

You'll need a left join on your admins table and a case expression in the select clause to determine if the row was found in the admins table or not.

You didn't fully describe your tables, but it would look something like this:

select u.*,
       case when a.userId is not null then 'yes' else 'no' end as is_admin
  from users u
  left join admins a
    on a.user_id = u.user_id
Comments