Query help with EXCEPT - SQL Server Forums

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

Query help with EXCEPT

Author

Topic

engcanada
Starting Member

39 Posts

Posted - 2011-06-13 : 20:18:18

Can someone tell me the problem with this query:
"select count(id) as totalid from register except select distinct idllc from resume"

it returns the number of the records in table Register not excluding the ones in the resume table.

Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-13 : 20:25:20

"EXCEPT returns any distinct values from the left query that are not also found on the right query." Would this be what you are looking for?

select count(id) as totalid from register 
where not exists (select * from resume where resume.idllc = register.id);

engcanada
Starting Member

39 Posts

Posted - 2011-06-13 : 20:40:38

Yes that's exactly what I am looking for!
Cheerrrrrrrrrrs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-14 : 04:03:45

or

select count(id) as totalid from
(
select id from register except select idllc from resume
) as t

Madhivanan

Failing to plan is Planning to fail

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-14 : 17:58:26

>> Can someone tell me the problem with this query: <<

Lots of thing are wrong. All the data elemetn names are screwed up. And so vague You confused rows and records.

SELECT COUNT(candidate_id) AS candidate_id_cnt
FROM Employment_Candidate_Register
EXCEPT
SELECT llc_id
FROM Resumes;

This is the SQL version of set difference, and it is part of the UNION [ALL], INTERSECT [ALL] and EXCEPT [ALL] family. They are the usual set operations from your high school math class, but the ALL option preserves dupicates

>> it returns the number of the records [sic: distinct rows] in Employment_Candidate_Register not excluding the ones in the resume table. <<

Yes, it does. The candidate_id and llc_id columns have to be Union-compatible for it to work.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources