how to put together or exclude the result from two or more select query

Question

Post reply

how to put together or exclude the result from two or more select query

Seattlemsp

SSC Eights!

Points: 932
More actions
August 21, 2012 at 11:09 am

#392994

there are two table
table 1
emplyoee ID
001
002
003
004
005
006
table 2
emplyoee ID
004
005
006
007
008
009
How can I write a query which produce below two results:
Result 1 --- list all the distinct emplyoee IDs
001
002
003
004
005
006
007
008
009
Result 2 ---- list all the emplyoee IDs in the table 1 but table 2
001
002
003
Thanks!

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There are several ways to do this but I prefer to use INTERSECT/EXCEPT for this type of thing.

http://msdn.microsoft.com/en-us/library/ms188055.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Something like this.

create table #table1 (employeeID int)

create table #table2 (employeeID int)

insert #table1

select 1 union all

select 2 union all

select 3 union all

select 4 union all

select 5 union all

select 6

insert #table2

select 4 union all

select 5 union all

select 6 union all

select 7 union all

select 8 union all

select 9

--this will show all employeeid in table1 that are not in 2

select employeeid

from #table1

EXCEPT

select employeeid

from #table2

--this will show all employeeid that are in both tables

select employeeid

from #table1

INTERSECT

select employeeid

from #table2

drop table #table1

drop table #table2

You might notice that I created sample data and tables in such a way that you can easily run queries against it. In the future you should consider posting like that. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 3

Add in Union for full concatenation. Intersect doesn't actually add sets together, it finds the data that's common to two sets.

Is this one a homework question? It kind of looks like it, but that might just be because of the sample data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

There are many, many ways to procuce each result set. For the first one you could use UNION (not UNION ALL).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001