I'm trying to do something similar to this:
CASE
WHEN number IN (1,2,3) THEN 'Y' ELSE 'N' END;
Instead I want to have a query in the place of the list, like so:
CASE
WHEN number IN (SELECT num_val FROM some_table) THEN 'Y' ELSE 'N' END;
I can't seem to get this to work. Also, here is an example of the query.
SELECT number, (CASE
WHEN number IN (SELECT num_val FROM some_table) THEN 'Y' ELSE 'N' END) AS YES_NO
FROM some_other_table;
Yes, it's possible. See an example below that would do what you are intending. The difference is that it uses
EXISTS
instead ofIN
.EDIT: I confess: I like the answers given by the others better personally.
However, there will be a difference between this query and the others depending on your data.
If for a value
number
in the tablesome_other_table
you can have many matching entries ofnum_val
in the tablesome_table
, then the other answers will return duplicate rows. This query will not.That said, if you take the
left join
queries given by the others, and add agroup by
, then you won't get the duplicates.