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
Solved

SQL JOIN - Show all Employees and Specific Records from joining table

Posted on 2011-03-25
6
197 Views
Last Modified: 2012-05-11
The scope on my project has changed a bit.  The query below worked fine until management decided that they wanted to show employee's that had zero time entered as well.

I now need to show a list of employee's hours (even if there is no record of hours entered), along with their Office, Skill Level and Department for a specifc date range.  Before I didn't have to show the employee if no time was entered.

 
Needed Output:

 EmployeeName        EBDate               EBChgHours EBNonHours OffName DeptName SKDesc
 ------------        ------               ---------- ---------- ------- -------- ------
 Doe, John E (1111)  2010-02-01 00:00:00  10.75      2.25       City    DeptA    Manager
 Abe, Jane A (2222)                       0          0          CityB   DeptB    Associate

Open in new window


Original Query:
 
SELECT     Employee.Emplname + ', ' + Employee.Empfname + ' (' + Employee.Empnum + ')' AS EmployeeName, EmpBudHRS.EBDate, EmpBudHRS.EBChgHours, 
                      EmpBudHRS.EBNonHours, Office.OffName, Department.DeptName, SkillLevel.SkDesc
FROM         Employee LEFT OUTER JOIN
                      EmpBudHRS ON Employee.ID = EmpBudHRS.EBEmpID RIGHT OUTER JOIN
                      Office ON Employee.Empoff = Office.OffID INNER JOIN
                      Department ON Employee.Empdept = Department.DeptID INNER JOIN
                      SkillLevel ON Employee.Emplevel = SkillLevel.SkID
WHERE     (EmpBudHRS.EBDate BETWEEN @begdate AND @enddate)
ORDER BY Employee.Emplname

Open in new window


Thanks
0
Comment
Question by:ITMcmcpa
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35214875
How this query works ?
SELECT  Employee.Emplname + ', ' + Employee.Empfname + ' (' + Employee.Empnum
        + ')' AS EmployeeName ,
        EmpBudHRS.EBDate ,
        ISNULL(EmpBudHRS.EBChgHours, 0) ,
        ISNULL(EmpBudHRS.EBNonHours, 0) ,
        Office.OffName ,
        Department.DeptName ,
        SkillLevel.SkDesc
FROM    Employee
        INNER JOIN Office ON Employee.Empoff = Office.OffID
        INNER JOIN Department ON Employee.Empdept = Department.DeptID
        INNER JOIN SkillLevel ON Employee.Emplevel = SkillLevel.SkID
        LEFT OUTER JOIN EmpBudHRS ON Employee.ID = EmpBudHRS.EBEmpID
WHERE   ( EmpBudHRS.EBDate BETWEEN @begdate AND @enddate )
ORDER BY Employee.Emplname

Open in new window

0
 

Author Comment

by:ITMcmcpa
ID: 35214921
Unfortunately this query still only pulls Employees that have a record (time entered) in the EmpBudHRS table.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35214946
Just to test - Try this query. Getting records those have no record in Hours table ?
SELECT * 
FROM Employee E
	INNER JOIN EmpBudHRS H ON E.ID = H.EBEmpID
WHERE H.EBDate BETWEEN @begdate AND @enddate

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 35215063
Hi ITMcmcpa,

If I have understood this correctly you are currently showing staff who had hours logged between two dates (@begdate AND @enddate)

and now you need to show all staff who fall into this bracket along with any staff who have no entry at all in the EmpBudHRS table.

If this is right then your current WHERE clause will exclude anyone with not entry in the EmpBudHRS table.

so try changing

WHERE     (EmpBudHRS.EBDate BETWEEN @begdate AND @enddate)

to

WHERE     (EmpBudHRS.EBDate BETWEEN @begdate AND @enddate) OR EmpBudHRS.EBDate IS NULL

and I believe you will get your missing details.

Hope this helps,

Mark
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 35215069
Try this one
SELECT  Employee.Emplname + ', ' + Employee.Empfname + ' (' + Employee.Empnum
        + ')' AS EmployeeName ,
        EmpBudHRS.EBDate ,
        ISNULL(EmpBudHRS.EBChgHours, 0) ,
        ISNULL(EmpBudHRS.EBNonHours, 0) ,
        Office.OffName ,
        Department.DeptName ,
        SkillLevel.SkDesc
FROM    Employee
        INNER JOIN Office ON Employee.Empoff = Office.OffID
        INNER JOIN Department ON Employee.Empdept = Department.DeptID
        INNER JOIN SkillLevel ON Employee.Emplevel = SkillLevel.SkID
        LEFT OUTER JOIN EmpBudHRS ON Employee.ID = EmpBudHRS.EBEmpID AND EmpBudHRS.EBDate BETWEEN @begdate AND @enddate
ORDER BY Employee.Emplname

Open in new window

0
 

Author Comment

by:ITMcmcpa
ID: 35215121
@RajkumarGS:
The last one seems to have done the trick.  Let me do a little more testing and then I will accept.
Thanks!
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 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