• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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.
0
dyarosh
Asked:
dyarosh
  • 4
1 Solution
 
PortletPaulfreelancerCommented:
have you tried 'left join' instead of 'join'?
0
 
PortletPaulfreelancerCommented:
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
0
 
PortletPaulfreelancerCommented:
sorry, more info.
Use INNER JOIN where the relationship MUST exist.
For any table where the relationship "might NOT" occur, use a LEFT JOIN

another candidate could be line 21 above (reason) - but I don't know your data model so I can't be exact.
0
 
dyaroshAuthor Commented:
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.
0
 
PortletPaulfreelancerCommented:
thank you. will look out for the new question :) Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now