Question:
Let’s say I have two tables, one with the columns PositionIDand SkillID and one with the columns EmployeeID and SkillID.If I do a SELECT SkillID FROM First_Table WHERE PositionID=7,I will get a list of SkillIDs that PositionID=7 requires.If I do a SELECT SkillID FROM Second_Table WHERE EmployeeID=8,I will get a list of SkillIDs that EmployeeID=8 has.
If I do an INNER JOIN on the two tables I can easily get a listof the SkillIDs that the employee BOTH requires AND has butwhat I really want is a list of the SkillIDs that theemployee does NOT have. Is there anyway to do this in one SELECT statement?
Answer:
You can solve for skills the employee does not have with the following query:
SELECT * FROM Employees WHERE employeeid=8 AND skillid NOT IN (SELECT skillid FROM Jobs WHERE positionid=7);I had an additional question, very similar to this one, but where thecombination of two columns was needed to determine the data thatdid not exist in the second table. Remember that you also canconcatenate the columns if the unique data is not found in asingle column.