How to have IN operator work to have hard coded values match

Question

Have accountId ={001,3456,78902,456}

I wanted to get the details of these accountId from existing table using IN clause and a left join on operation, how should I form the query? Being new to DB end I am not able to put the correct pieces together:

select t1.col2, case when CASE WHEN t1.col3 is null THEN 'False' ELSE 'True' END as value
FROM table2 t2 left join table1 t1 on t2.col1=t1.col1
WHERE accountID IN (001,3456,78902,456); //The query is wrong because, I wanted to map using those accountID and get the data?

Any quick help on forming the query would be great

The Existing table have accountId as one of the columns.

`accountid` is part of which table? Always provide table definitions and your version of Postgres with questions like that. And an explanation of what you are trying to achieve. — Erwin Brandstetter, Jun 06 '14 at 17:11

Erwin Brandstetter · Answer 1 · 2014-06-06T17:17:42.597

Guessing ...

SELECT t2.col1, t1.col2, (t1.col3 IS NULL) AS value
FROM   table2 t2
LEFT   JOIN table1 t1 USING (col1)
WHERE  t2.accountid IN (1,3456,78902,456); -- t1 or t2 ???

I added t2.col1 or you might get rows with only NULL values.
Using the simpler (t1.col3 IS NULL) AS value which returns boolean TRUE / FALSE directly.
Also using the simpler USING to join on a column of the same name.

If accountid is in table1 as well, you don't need to join at all ..

SELECT t1.col2, (t1.col3 IS NULL) AS value
FROM   table1
WHERE  t1.accountid IN (1,3456,78902,456);

How to have IN operator work to have hard coded values match

1 Answers1