asked on

SQL Help

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
FROM EMP_OWNER.EMP_EmployeeHistory 
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

Open in new window

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.

PortletPaul

have you tried 'left join' instead of 'join'?

PortletPaul

like this, note line 20:

SELECT
      EffectiveDate
    , ReasonDesc
    , TitleDesc
    , LastName || ', ' || FirstName AS ManagerName
    , Division
    , Department
    , GroupDesc
    , FunctionDesc
    , LocationDesc
    , SubLocationDesc
FROM EMP_OWNER.EMP_EmployeeHistory
INNER JOIN EMP_OWNER.EMP_Title       ON EMP_EmployeeHistory.TitleID             = EMP_Title.TitleID
INNER JOIN EMP_OWNER.EMP_Employee    ON EMP_EmployeeHistory.ManagerSupervisorID = EMP_Employee.EmployeeID
INNER JOIN EMP_OWNER.EMP_Division    ON EMP_EmployeeHistory.DivisionID          = EMP_Division.DivisionID
INNER JOIN EMP_OWNER.EMP_Department  ON EMP_EmployeeHistory.DepartmentID        = EMP_Department.DepartmentID
INNER JOIN EMP_OWNER.EMP_Group       ON EMP_EmployeeHistory.GroupID             = EMP_Group.GroupID
INNER JOIN EMP_OWNER.EMP_Function    ON EMP_EmployeeHistory.FunctionID          = EMP_Function.FunctionID
INNER JOIN EMP_OWNER.EMP_Location    ON EMP_EmployeeHistory.LocationID          = EMP_Location.LocationID
LEFT  JOIN EMP_OWNER.EMP_SubLocation ON EMP_EmployeeHistory.SubLocationID       = EMP_SubLocation.SubLocationID
INNER JOIN EMP_OWNER.EMP_Reason      ON EMP_EmployeeHistory.ReasonID            = EMP_Reason.ReasonID 
WHERE EMP_EmployeeHistory.EmployeeID = 776
ORDER BY EMP_EmployeeHistory.EmployeeHistoryID DESC

Open in new window

and you may require more than one left join

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dyarosh

ASKER

Thank you. I tried Left Join before posting but did not apply it to all of the joins. Thanks. I will be posting another SQL question regarding this query if you are looking for more points.

PortletPaul

thank you. will look out for the new question :) Cheers, Paul