sql query that filters on a related table
Posted on 2010-11-16
I have two tables that I need to make a report from, but I can't figure out the SQL.
Basically I want all rows from table SAPModules, and then add some fields from SAPSkills.
SAPModules lists all available choices, while SAPSkills lists the already chosen skills for each person.
The result should be a list of all the SAP modules available plus added data in those rows that exists in SAPSkills.
My problem starts when I want to filter those lists by PersonID in SAPSkills. How do I cange the SQL so that I can print out a list of all SAPModules, but only include the extra data from SAPSkills relevant for each individual person?
SELECT SAPModules.Code + N' ' + SAPModules.Name AS SAPModule, SAPSkills.StartDate, SAPSkills.EndDate, SAPSkills.PersonID
FROM SAPSkills RIGHT OUTER JOIN
SAPModules ON SAPSkills.SAPModuleID = SAPModules.SAPModuleID
WHERE (SAPSkills.PersonID = @PersonID)