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)
 Sql Statement

Author  Topic 

uiranejeb
Starting Member

1 Post

Posted - 2005-02-20 : 19:03:42
I have 3 tables:
Employee (EmployeeId, FirstName, LastName)
Project(ProjectId, Name)
EmployeeProject(EmployeeId, ProjectId)

I need to find out the employees who have worked for all the projects in the PROJECT table (just ONE select statement).

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-20 : 22:36:23
Here are a couple of ways. You should compare the execution plans to see which one Sqlserver likes better.

Select e.*
From (
Select employeeID
,ProjectCount = count(*) --count(distinct ProjectID) --if employeeid/projectid combinations are not unique
From employeeProject
Group by employeeID
having count(*) = (Select count(*) projectCount from Project)
) as epc
JOIN Employee e
ON epc.employeeID = e.employeeID


Select e.*
From (--Get employees NOT in subquery results
Select distinct ep1.EmployeeID
From EmployeeProject ep1
Left JOIN (--Get employees that have NOT worked on all projects
Select ep2.employeeID
From Project p2
Left JOIN EmployeeProject ep2
on p2.projectID = ep2.projectID
Where ep2.projectID is NULL
) as empExcl
ON empExcl.employeeID = ep1.employeeID
Where empExcl.employeeID is NULL
) as empIncl
JOIN Employee e
ON e.employeeID = empIncl.employeeID


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -