Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

comparing list to master list Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 12:25 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 681, Visits: 1,603
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');
Post #1442607
Posted Tuesday, April 16, 2013 1:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 6,826, Visits: 14,057
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442617
Posted Tuesday, April 16, 2013 6:11 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 681, Visits: 1,603
Thank you.
Post #1442701
Posted Tuesday, April 16, 2013 7:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:40 AM
Points: 400, Visits: 38
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.
Post #1442735
Posted Tuesday, April 16, 2013 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 6,826, Visits: 14,057
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442766
Posted Tuesday, April 16, 2013 8:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 6,826, Visits: 14,057
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse