SQL Help

Posted on 2013-06-24
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 48

Expert Comment

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

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 48

Accepted Solution

PortletPaul earned 500 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 48

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

730 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