• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

SQL Statement SELECT * FROM Parent table WHERE NOT EXISTS in child table

Two Tables,

Employees  e
    EmployeeID int  PK
    LastName...

EmpTrainingRequirements   tr
    TrainingRequirementID  int Pk
    EmployeeID   int   Fk

I need to return only those Employees who do not have a specific TrainingRequirementID (97) in the EmpTrainingRequirements table.

I have tried several methods but can't seem to get the correct return. Any ideas?
select LastName  as Employee, 
e.ssn
FROM Employees e 
LEFT OUTER JOIN
EmpTrainingRequirements tr ON e.EmployeeID = tr.EmployeeID 
where NOT EXISTS (Select * FROM EmpTrainingRequirements  where TrainingRequirementID = 97)

Open in new window

0
AkAlan
Asked:
AkAlan
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You are missing the connection of EmployeeID in the NOT EXISTS. You won't need the OUTER JOIN then.
0
 
Kevin CrossChief Technology OfficerCommented:
i.e.,
SELECT e.LastName  as Employee, e.ssn
FROM Employees e 
WHERE NOT EXISTS (
   SELECT 1 
   FROM EmpTrainingRequirements tr 
   WHERE e.EmployeeID = tr.EmployeeID
   AND tr.TrainingRequirementID = 97
)

Open in new window

0
 
AkAlanAuthor Commented:
Worked great, Thanks.
0
 
HainKurtSr. System AnalystCommented:
or using not in...

SELECT e.LastName  as Employee, e.ssn
FROM Employees e
WHERE EmployeeID not in (
   SELECT EmployeeID
   FROM EmpTrainingRequirements tr
   WHERE TrainingRequirementID = 97
)
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now