Solved

SQL Help

Posted on 2013-06-24
5
339 Views
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.
0
Comment
Question by:dyarosh
  • 4
5 Comments
 
LVL 48

Expert Comment

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

Expert Comment

by:PortletPaul
ID: 39273648
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
 
LVL 48

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:dyarosh
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.
0
 
LVL 48

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now