CODEWITHSUNDEEP
sqlsql-server-2008
Barefooter
Barefooter

Reputation: 61

How to get the result as 'Null' or 'Not found' for the fields which don't exist in the table

There's a bulk file which contains the loginID of all the users in a region. Now, i need to get the username of all the IDs from the DB but there are many invalid login IDs in the file and they don't exist in the MS Sql DB.

I tried to give the below query but it will give the result only if the login ID is present. Is there any possibility to get the result as 'Null' or 'Not found' if the loginID is not found in the DB?

select username from contacts where loginID in ('xyz','abc',... upto 3K records)

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use a left join . . . to do this you need start with a "table" of the ids. So:

select v.id, c.username
from (values ('xyz'), ('abc'), . . .
     ) v(id) left join
     contacts c
     on c.id = v.id;

Note: If your list is already coming from a query in the database, then either include that query in this query (as a CTE or subquery) or save the results in a temporary table.

Upvotes: 1

Related Questions