Solved

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

Posted on 2011-09-12
4
259 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 42
Group by and order by clause 28 47
default constraint within a function 3 36
Need help debbuging stored procedure 21 32
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

930 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now