Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 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

18 Experts available now in Live!

Get 1:1 Help Now