Solved

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

Posted on 2011-09-12
4
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 60

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 60

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 56

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

631 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