USE OF Not In in MS SQL SERVER

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

USE OF Not In in MS SQL SERVER

Author

Topic

julmwash
Starting Member

1 Post

Posted - 2011-06-23 : 09:04:37

hi everyone, how can i combine the below two sql statement into one statement.

USE CHANGOI
GO
SELECT PersonalID, PFNO, NSSF_No, StaffName,DName From Lowgradestaff WHERE Division=1 and PersonalID Not In (SELECT DISTINCT Ecode FROM DailyWorkAllocation WHERE TskDate = '06/12/2011'AND ActDivision=1 )
GO
SELECT PersonalID, PFNO, NSSF_No, StaffName,DName From Lowgradestaff WHERE Division=1 and PersonalID Not In (SELECT DISTINCT EmpNo FROM Plucking WHERE WDate = '06/12/2011' AND HarvestDivision=1)
GO

the idea is to get one result set that compare between the three tables.

julmwash

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-23 : 09:32:29

SELECT PersonalID, PFNO, NSSF_No, StaffName,DName From Lowgradestaff WHERE Division=1 and PersonalID Not In (
SELECT Ecode FROM DailyWorkAllocation WHERE TskDate = '06/12/2011'AND ActDivision=1
UNION
SELECT EmpNo FROM Plucking WHERE WDate = '06/12/2011' AND HarvestDivision=1
)

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 10:16:27

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources