joins joins joins - 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

SQL Server 2000 Forums

Transact-SQL (2000)

joins joins joins

Author

Topic

Dirty_Howi
Starting Member

12 Posts

Posted - 2005-04-27 : 10:05:10

i have two views that return data, one returns people who currently work at this facility(left hand table) one returns people who currently work at the facility, and have taken a particular class.

there are 877 current employees, of which 832 have had the class, how do i join those two data sources to get the 45 people who have not had the class.??

help quick, sr. management is breathing down my neck.

When i'm right, no one remembers, when i'm wrong no one forgets.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-27 : 10:50:23

You could do this 2 ways

1.
SELECT EmployeeId
FROM EmployeeView
WHERE NOT EXISTS (SELECT EmployeeID FROM ClassView WHERE........)

2.
SELECT E.*
FROM EmployeeView E LEFT OUTER JOIN ClassView C
ON E.EmployeeID = C.EmployeeID
WHERE C.EmployeeID IS NULL

Andy

Beauty is in the eyes of the beerholder

Dirty_Howi
Starting Member

12 Posts

Posted - 2005-04-27 : 11:57:21

not exists didnt' work, not in did ;)

When i'm right, no one remembers, when i'm wrong no one forgets.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-27 : 12:57:05

Oops


SELECT EmployeeId
FROM EmployeeView
WHERE NOT EXISTS (SELECT EmployeeID FROM ClassView WHERE ClassView.EmployeeId = EmployeeView.EmployeeId AND......)

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources