Nazaroth
asked on
Sql - Which employees does not have the required competencies
Hmm - or possibly this:
SELECI Employees.ID, FirstName,LastName,Compent encies.Tit le, Competencies.ID
FROM (Employees LEFT JOIN EmployeeCompetencies ON Employees.ID = EmployeeCompetencies.Emplo yeeID) LEFT JOIN Competencies ON EmployeeCompetencies.Compe tencyID = Competencies.ID
WHERE Competencies.ID NOT IN
(SELECT Competencies.ID FROM ((Employees LEFT JOIN EmployeePosition ON Employees.ID = EmployeePosition.EmplyeeID ) LEFT JOIN Positions ON EmployeePosition.PositionI D = Positions.ID) LEFT JOIN RequiredCompetencies ON Positions.ID = RequiredCompetencies.Posit ionID)
The first select pulls employees and their competencies.
The second pulls "required competencies" based on employee and position.
The NOT IN should restrict the employees pulled overall to those that do not have the "required competencies"
SELECI Employees.ID, FirstName,LastName,Compent
FROM (Employees LEFT JOIN EmployeeCompetencies ON Employees.ID = EmployeeCompetencies.Emplo
WHERE Competencies.ID NOT IN
(SELECT Competencies.ID FROM ((Employees LEFT JOIN EmployeePosition ON Employees.ID = EmployeePosition.EmplyeeID
The first select pulls employees and their competencies.
The second pulls "required competencies" based on employee and position.
The NOT IN should restrict the employees pulled overall to those that do not have the "required competencies"
ASKER
Thank you for your fast reply!
Your answers does not help me though...
The first expression can be translated to "Which employees have this specific competency?" and that is helpful, but not precisely what i hade in mind.
The second expression results in an empty set, and I know for sure that that is not the correct answer. I'v been fiddling with the syntax for several hours today but i cant get it to work..
If I, in the NOT IN select change from CompetencyID to RequiredCompetencies.Compe tencyID i get a list of competencies that the employees have but that are not required, but i can't seem to get the opposite!
I should mention that I'm using Access 2010
Your answers does not help me though...
The first expression can be translated to "Which employees have this specific competency?" and that is helpful, but not precisely what i hade in mind.
The second expression results in an empty set, and I know for sure that that is not the correct answer. I'v been fiddling with the syntax for several hours today but i cant get it to work..
If I, in the NOT IN select change from CompetencyID to RequiredCompetencies.Compe
I should mention that I'm using Access 2010
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It solves the problem, although i'm not sure about the efficiency of this solution.
SELECT Employees.ID, LastName, FirstName
FROM ((Employees LEFT JOIN EmployeePosition ON Employees.ID = EmployeePosition.EmplyeeID
WHERE RequiredCompetencies.Compe