SQL stored procedure Join question
currently the stored proc returns one record based on a employee_number that is passed as a parameter. If the surpervisor_id is NULL, there is no record returned. If I want to return a record with all the other fields, and have a NULL in the supervisor_id field, how would I configure that?
SELECT DISTINCT
tblPeople.PERSON_ID, tblPeople.FIRST_NAME, tblPeople.LAST_NAME,
tblPeople.EMPLOYEE_NUMBER, tblPeople.EMAIL_ADDRESS,
tblAddress.ADDRESS_LINE1, tblAddress.ADDRESS_LINE2, tblAddress.ADDRESS_LINE3,
tblAddress.TOWN_OR_CITY, tblAddress.STATE, tblAddress.COUNTRY,
tblAddress.POSTAL_CODE, tblPeople.PHONE_NUMBER,
tblPeople.EFFECTIVE_START_DATE, tblPeople.EFFECTIVE_END_DATE,
tblLocation.LOC_CODE,
pp2.FIRST_NAME AS Expr1, pp2.LAST_NAME AS Expr2,
pp2.EMPLOYEE_NUMBER AS Expr3, tblAssignment.SUPERVISOR_ID,
tblAssignment.ATTRIBUTE2, tblAssignment.ATTRIBUTE3,
tblPeople.INSTANCE_ID, tblAssignment.LOC_ID
FROM
tblPeople AS tblPeople
RIGHT OUTER JOIN
tblAddress AS tblAddress ON tblPeople.PERSON_ID = tblAddress.PERSON_ID
AND tblPeople.INSTANCE_ID = tblAddress.INSTANCE_ID
INNER JOIN
tblAssignments AS tblAssignment ON tblPeople.PERSON_ID = tblAssignment.PERSON_ID
AND tblPeople.INSTANCE_ID = tblAssignment.INSTANCE_ID
INNER JOIN
Locations AS Location ON Location.LOC_ID = tblAssignment.LOC_ID
开发者_如何学JAVA AND Location.INSTANCE_ID = tblAssignment.INSTANCE_ID
INNER JOIN
tblPeople AS pp2 ON pp2.PERSON_ID = tblAssignment.SUPERVISOR_ID
AND pp2.INSTANCE_ID = tblAssignment.INSTANCE_ID
WHERE
tblPeople.EMPLOYEE_NUMBER = '3298'
AND tblPeople.INSTANCE_ID = 1
AND tblAssignment.Assignment_Status = 'Current'
Most likely, you just need to change this JOIN
INNER JOIN
tblPeople AS pp2 ON pp2.PERSON_ID = tblAssignment.SUPERVISOR_ID
AND pp2.INSTANCE_ID = tblAssignment.INSTANCE_ID
to an LEFT OUTER JOIN
LEFT OUTER JOIN
tblPeople AS pp2 ON pp2.PERSON_ID = tblAssignment.SUPERVISOR_ID
AND pp2.INSTANCE_ID = tblAssignment.INSTANCE_ID
If that SUPERVISOR_ID
IS NULL, then the columns from the pp2
table will be NULL, but you should still get data for all other columns back.
精彩评论