Getting items in a list which were *not* found

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

SQL Server 2000 Forums

SQL Server Development (2000)

Getting items in a list which were *not* found

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-07-26 : 00:27:23

When doing a query using "IN(<list of field values>)", is there a way of getting MSSQL to return the items in the list which were not found?

That is, if searching for a list of names: IN('andrew','frank','barry') but only 'andrew' exists in the database, how can I get a resultset of ['frank','barry']? Is that possible?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 02:36:39

Without table structure and example data it isn't possible to understand what you want.
How should a resultset bring back values that are not in the table?

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-07-26 : 04:43:56

turn the CSV 'andrew', 'frank', 'barry' into a in-line table and 'left join' it to your data.
aka

select b.name
(
select andrew as name
union all
select frank
union
select barry
) a
left join yourdata b on b.code = a.name
where b.code is null

your hard bit is to turn the IN list into the mini-table "a"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources