Finding Data that’s in One Table but not Another, Part 2

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: