[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


SQL Help

Posted on 2013-06-24
Medium Priority
Last Modified: 2013-06-25
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.
Question by:dyarosh
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
LVL 49

Expert Comment

ID: 39273635
have you tried 'left join' instead of 'join'?
LVL 49

Expert Comment

ID: 39273648
like this, note line 20:
    , 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
LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39273668
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.

Author Closing Comment

ID: 39274407
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.
LVL 49

Expert Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question