comparing list to master list

Question

comparing list to master list

KoldCoffee

SSCoach

Points: 18971
More actions
April 16, 2013 at 12:25 am

#271068

How would you write a query that would return only the items in the where clause, which were not found in the table? ie. 'fff','ggg','hhh','iii' are not found in the table. I could build a second table with all the values in the where clause and do an except, but that is too much work...
create table List
(id int,
name varchar(20)
)
insert into List
values
(1,'aaa'),
(2,'bbb'),
(3,'ccc'),
(4,'ddd'),
(5,'eee'),
(6,'jjj'),
(7,'kkk'),
(8,'lll');
select * from List
where name not in ('ooo','bbb','ccc','ddd','eee','fff', 'ggg', 'hhh', 'iii', 'jjj','kkk','lll');

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT d.*

FROM (VALUES ('ooo'),('bbb'),('ccc'),('ddd'),('eee'),('fff'),('ggg'),('hhh'),('iii'),('jjj'),('kkk'),('lll')) d (name)

WHERE NOT EXISTS (SELECT 1 FROM List l WHERE l.name = d.name)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

KoldCoffee

SSCoach

Points: 18971

April 16, 2013 at 6:11 am

#1606780

Thank you.

Pothiraj R SSC-Addicted Points: 486 More actions · Answer 3

Hi Chris,

Thanks for the query. I didn't use the syntax like this.

FROM (VALUES ('ooo'),('bbb'),('ccc'),('ddd'),('eee'),('fff'),('ggg'),('hhh'),('iii'),('jjj'),('kkk'),('lll')) d (name)

In the same scenario, I will use UNION ALL with SELECT or insert the values in another table. Your query will be helpful for me. Thanks again.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

KoldCoffee (4/16/2013)
Thank you.

Any time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Pothiraj R (4/16/2013)
Hi Chris,
Thanks for the query. I didn't use the syntax like this.
FROM (VALUES ('ooo'),('bbb'),('ccc'),('ddd'),('eee'),('fff'),('ggg'),('hhh'),('iii'),('jjj'),('kkk'),('lll')) d (name)
In the same scenario, I will use UNION ALL with SELECT or insert the values in another table. Your query will be helpful for me. Thanks again.

UNION ALL with SELECT is just another table-valued constructor but handy to know if you're connected to pre-2K8.

You're welcome.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden