sgude0
asked on
sql query that filters on a related table
Hi
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)
TIA
Dennis
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)
TIA
Dennis
>> 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?
Not sure on what you meant by Print out, kindly clarify
Also post some sample records to explain what you are trying to achieve.
Not sure on what you meant by Print out, kindly clarify
Also post some sample records to explain what you are trying to achieve.
ASKER
This still doesn't work, as I now only get records from SAPSkills with fields from SAPModules, I want it the other way around.
The fields in the resulting list should be like this:
1: SAPModules.Name (every line)
2: SAPSkills.StartDate (only if selected PersonID has one, else NULL)
2: SAPSkills.EndDate (only if selected PersonID has one, else NULL)
Re
Dennis
The fields in the resulting list should be like this:
1: SAPModules.Name (every line)
2: SAPSkills.StartDate (only if selected PersonID has one, else NULL)
2: SAPSkills.EndDate (only if selected PersonID has one, else NULL)
Re
Dennis
>> WHERE (SAPSkills.PersonID = @PersonID)
Since you have this WHERE condition passed, your query would bring records only if you have records in SAPSkills table since PersonID is present in SAPSkills table alone.
Else it won't bring any records.
Can you kindly clarify your exact requirements..
Since you have this WHERE condition passed, your query would bring records only if you have records in SAPSkills table since PersonID is present in SAPSkills table alone.
Else it won't bring any records.
Can you kindly clarify your exact requirements..
ASKER
OK, I think I understand that problem. Let's do it step by step ;-)
The following statement gets the list I want:
SELECT Code + N' ' + Name AS SAPModule
FROM SAPModules
I will give each employee this list (to update their CV's). However, since most employees have already registered skills in the past, this information should be added to their list so they dont have to re-do the registration, just update the new stuff they've learned since the last registration.
I've included an Excel sheet with the basic data.
Re
Dennis
SkillsUpdate.xls
The following statement gets the list I want:
SELECT Code + N' ' + Name AS SAPModule
FROM SAPModules
I will give each employee this list (to update their CV's). However, since most employees have already registered skills in the past, this information should be added to their list so they dont have to re-do the registration, just update the new stuff they've learned since the last registration.
I've included an Excel sheet with the basic data.
Re
Dennis
SkillsUpdate.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help though.
SELECT SAPModules.Code + N' ' + SAPModules.Name AS SAPModule, SAPSkills.StartDate, SAPSkills.EndDate, SAPSkills.PersonID
FROM SAPModules
LEFT JOIN SAPSkills ON SAPSkills.SAPModuleID = SAPModules.SAPModuleID
WHERE (SAPSkills.PersonID = @PersonID)