IN query, obtaining the missing results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IN query, obtaining the missing results

Author

Topic

Jnetik
Starting Member

5 Posts

Posted - 2011-11-03 : 10:12:17

Hello,

This is my example line of code:

select NAME_ID from NAMES where NAME_ID IN ('Bob',
'Joe',
'Sarah',
'Dave')

Let's say I run the above and inside my database 'NAMES' I have the entries 'Bob' and 'Sarah'.

The returned output will look like this

NAME_ID
Bob
Sarah

My question is as follows, is it possible to return my entire input list for comparison with the output result, without creating a new table (I only have read-only access).

For example, could I get something like this:

NAME_ID INPUT
Bob Bob
Sarah Sarah
Joe
Dave

Or even just this would be helpful

INPUT
Joe
Dave

NOTE: There will be hundreds of other names in the database so I can't just ask it to return anything that is not 'Bob' or 'Sarah'

Thanks in advance

J

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-03 : 10:51:42

Try this:


select distinct dt.Name_Id as Searched, n.Name_Id as inTable
from

(select 'Bob' as Name_Id union all
 select 'Joe' as Name_Id union all
 select 'Sarah' as Name_Id union all
 select 'Dave' as Name_Id) dt

left join Names n on n.Name_Id = dt.Name_Id

No, you're never too old to Yak'n'Roll if you're too young to die.

Jnetik
Starting Member

5 Posts

Posted - 2011-11-03 : 11:52:58

That's great thanks. One more thing if that's OK...

Is it possible to put a second column input into the SQL to be returned back out with the search results.

If I have a list of peoples names and ages, and I want to see if these peoples names are in the database, can I return a list of matches and non-matches this additional age information.

For example,

The output from your code above yields the following,

Searched inTable
Joe (null)
Dave (null)
Bob Bob
Sarah Sarah

If I know that Bob has age 20, Joe, Sarah, Dave have ages 19, 18, 17 respectively. (these ages are supplied by me and are not present in the database)

Can I have something like this output:

Searched inTable Input_age
Joe (null) 19
Dave (null) 18
Bob Bob 20
Sarah Sarah 17

Thanks again so much, I've just played around for an hour trying do the above.

J

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-03 : 12:10:16

[code]
select distinct dt.Name_Id as Searched, n.Name_Id as inTable, dt.Input_age
from

(select 'Bob' as Name_Id, 20 as Input_age union all
select 'Joe' as Name_Id, 19 as Input_age union all
select 'Sarah' as Name_Id, 17 as Input_age union all
select 'Dave' as Name_Id, 18 as Input_age) dt

left join Names n on n.Name_Id = dt.Name_Id
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Jnetik
Starting Member

5 Posts

Posted - 2011-11-03 : 13:19:46

Thank-you very much, that's awesome :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-03 : 13:21:24

welcome