Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 24, 1997

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

Question:
Let's say I have two tables, one with the columns PositionID and 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 list of the SkillIDs that the employee BOTH requires AND has but what I really want is a list of the SkillIDs that the employee 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 the combination of two columns was needed to determine the data that did not exist in the second table. Remember that you also can concatenate the columns if the unique data is not found in a single column.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date