CODEWITHSUNDEEP
sqlsql-server-2012
Caleb Palmquist
Caleb Palmquist

Reputation: 458

SELECT Values in one table that don't match another table but also reference another column?

I realize that probably doesn't make sense so let me try and explain:

I have two tables with the followings columns (just relevant ones):

positTraining (positID, trainID)
empTraining (empID, trainID)

The positTraining table contains a listing of position ids along with any training ids the position requires.

The empTraining table contains a listing of employee ids along with any current training ids the employee has completed.

I want to select only the training ids required by the position that the employee has not already completed.

So, if Employee A has completed training 1 & 3 but Position X requires training 1 & 2, I want to select the trainID for the missing training (2).

I hope that makes sense.

I have searched google a million different ways and checked here on SO as well but cannot find a working answer due to this extra requirement:

I want to ONLY select the trainIDs based on the empID. Reasoning behind this is due to the fact that empID=2 may have the correct match but emp=1 may not.

The closest I've got all afternoon is by using the following:

select positID, trainID
from positTraining
except
select empID, trainID
from empTraining

Which returns:

positID  |  trainID
1        |  2

This is correct but only because of the current records. As soon as another employee is put in the database with the correct match this will stop working.

I have tried using several different methods:

JOINS
NOT IN
NOT EXIST

I'm sure this is way easier than I am making it but I just can't figure it out.

Thanks for any and all help, I'll be glad to add any extra details, just let me know.

EDIT 1
I'm using SQL Server 2012 Express

EDIT 2

This is what I've ended up with, I think this is going to work:

SELECT 
   pt.trainID AS positTrainID, 
   et.trainID AS empTrainID, 
   e.empFirst + ' ' + e.empLast  AS empName
FROM 
   positTraining pt, 
   empTraining et, 
   emps e
WHERE 
   pt.positID = 1 AND
   e.empID = 1 AND 
   pt.trainID not in (
      SELECT et.trainID FROM empTraining et WHERE et.empID = 1)
      AND pt.trainID = et.trainID

Result:

positTrainID    empTrainID  empName
2                   2           Emp1

Thanks for the help!

Upvotes: 4

Views: 9244

Answers (1)

VagnerPl
VagnerPl

Reputation: 166

I think this would work:

SELECT 
   positID, trainID 
FROM 
   positTraining 
WHERE 
   positID = X 
   AND trainID not in (
      SELECT trainID FROM empTraining WHERE empID = A)

Considering X as the positionId you are looking for the Employee A

Upvotes: 4

Related Questions