Solved

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

Posted on 2011-09-12
4
266 Views
Last Modified: 2012-05-12
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
Comment
Question by:AkAlan
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36524527
You are missing the connection of EmployeeID in the NOT EXISTS. You won't need the OUTER JOIN then.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36524536
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
 
LVL 6

Author Closing Comment

by:AkAlan
ID: 36524716
Worked great, Thanks.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36524721
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 8 51
SSRS 2013 - Creating a summarized report 19 49
Find results from sql within a time span 11 46
sql how to count case when 4 25
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question