I have a complicated SQL that I need help with. Basically I have a History table that has a lot of IDs in it. When displaying the information, I want to display the description based on the ID which means I have to join a bunch of tables. The problem that I am having is that not all of the ids will have valid values (i.e. a value doesn't have to be provided). When that happens, I don't get any records returned. Here is my SQL:
SELECT EffectiveDate, ReasonDesc, TitleDesc, LastName || ', ' || FirstName As ManagerName, Division, Department, GroupDesc, FunctionDesc, LocationDesc, SubLocationDesc
JOIN EMP_OWNER.EMP_Title ON EMP_Title.TitleID = EMP_EmployeeHistory.TitleID
JOIN EMP_OWNER.EMP_Employee ON EMP_Employee.EmployeeID = EMP_EmployeeHistory.ManagerSupervisorID
JOIN EMP_OWNER.EMP_Division ON EMP_Division.DivisionID = EMP_EmployeeHistory.DivisionID
JOIN EMP_OWNER.EMP_Department ON EMP_Department.DepartmentID = EMP_EmployeeHistory.DepartmentID
JOIN EMP_OWNER.EMP_Group ON EMP_Group.GroupID = EMP_EmployeeHistory.GroupID
JOIN EMP_OWNER.EMP_Function ON EMP_Function.FunctionID = EMP_EmployeeHistory.FunctionID
JOIN EMP_OWNER.EMP_Location ON EMP_Location.LocationID = EMP_EmployeeHistory.LocationID
JOIN EMP_OWNER.EMP_SubLocation ON EMP_SubLocation.SubLocationID = EMP_EmployeeHistory.SubLocationID
JOIN EMP_OWNER.EMP_Reason ON EMP_Reason.ReasonID = EMP_EmployeeHistory.ReasonID
WHERE EMP_EmployeeHistory.EmployeeID = 776
ORDER BY EMP_EmployeeHistory.EmployeeHistoryID DESC
Right now I'm hardcoding the EmployeeID but eventually it will be provided by a parameter. The History table contains 3 records but only 1 record is returned if I remove the references to the SublocationDesc and Join.
So to summarize, I want to return all the EmployeeHistory records for the ID specified and translate the ID values with the descriptive text from the corresponding table. Any help is greatly appreciated.