Sql join return only one row from left table

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have the following tables Employee, EmployeeManager, EmployeeLeavesAssigned, EmployeeLeavesTaken, EmployeeLeaveCashed

Employee 1 to Many EmployeeManager (One employee can have multiple managers)
Employee 1 to Many EmployeeLeavesAssigned (one record for each employee per calendar year)
Employee 1 to Many EmployeeLeavesTaken (multiple records for each employee per calendar year)
Employee 1 to Many EmployeeLeaveCashed (multiple records for each employee per calendar year)

Now when I am trying create a SP to get list all leaves cashed by employees
it gives me duplicate row for each record in the EmployeeLeaveCashed because of the EmployeeManager table

I am using left join and the left table is EmployeeLeaveCashed and right tables are Employee & Employee Manager. Even distinct does not help.

What should I do to eliminate this duplicate row.

Here is the query
SELECT
ED.RefEmpID AS [EmpID]
,ED.EmpFirstName
,ED.EmpMiddleName
,ED.EmpLastName
,EMG.RefEMGID AS [ManagerID]
,ELC.RefELCID AS [ELCID]
,ELC.CashedDate
,ELC.CashedCount
,ELC.RefCalendarPeriodID AS [APID]
,ELC.PeriodStartDate
,ELC.PeriodEndDate
,LTY.RefLeaveTypeID AS [LTYID]
,LTY.LeaveTypeName
FROM EmployeeLeaveCashed ELC
LEFT JOIN CalendarPeriod CP ON ELC.RefCalendarPeriod = CP.RefCalendarPeriod
LEFT JOIN LeaveType LTY ON ELC.RefLeaveTypeID = LTY.RefLeaveTypeID
LEFT JOIN Employee ED ON ELC.RefEmpID = ED.RefEmpID
LEFT JOIN EmployeeManager EMG ON ED.RefEmpID = EMG.RefEmpID

What I have tried:

I removed the EmployeeManager table from the join. But I need to show the Manager name. So I need to have in the SP.

Posted 27-Feb-20 19:04pm

Christopher Fernandes

Updated 27-Feb-20 20:32pm

v2

Add a Solution

Comments

Jörgen Andersson 28-Feb-20 1:29am

Can you show us the query?

Christopher Fernandes 28-Feb-20 1:40am

SELECT
ED.RefEmpID AS [EmpID]
,ED.EmpFirstName
,ED.EmpMiddleName
,ED.EmpLastName
,EMG.RefEMGID AS [ManagerID]
,ELC.RefELCID AS [ELCID]
,ELC.CashedDate
,ELC.CashedCount
,ELC.RefCalendarPeriodID AS [APID]
,ELC.PeriodStartDate
,ELC.PeriodEndDate
,LTY.RefLeaveTypeID AS [LTYID]
,LTY.LeaveTypeName
FROM EmployeeLeaveCashed ELC
LEFT JOIN CalendarPeriod CP ON ELC.RefCalendarPeriod = CP.RefCalendarPeriod
LEFT JOIN LeaveType LTY ON ELC.RefLeaveTypeID = LTY.RefLeaveTypeID
LEFT JOIN Employee ED ON ELC.RefEmpID = ED.RefEmpID
LEFT JOIN EmployeeManager EMG ON ED.RefEmpID = EMG.RefEmpID

Maciej Los 28-Feb-20 1:58am

Please, use Improve Question[^] widget and add above content.

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-02-27T20:32:00

I'm pretty sure it's your DB design that tripping you up - it doesn't sound right at all.
In particular:

Employee 1 to Many EmployeeManager (One employee can have multiple managers)

Seems wrong: an employee will normally have one manager, a manager will manage multiple employees.
Similarly:

Employee 1 to Many EmployeeLeavesAssigned (one record for each employee per calendar year)
Employee 1 to Many EmployeeLeavesTaken (multiple records for each employee per calendar year)
Employee 1 to Many EmployeeLeaveCashed (multiple records for each employee per calendar year)

Doesn't sound right either.
I'd suggest that Managers have Employees; Employees have YearlyLeaveRecords; LeaveBooked have YearlyLeaveRecords; LeaveTaken have YearlyLeaveRecords - this would reflect the "real world" situation far better, and will probably get rid of the problem.

So Employees have a Foreign Key to Managers; YearlyLeaveRecords have a Foreign Key to Employees; LeaveBooked and LeaveTaken have Foreign Keys to YearlyLeaveRecords.

Jörgen Andersson · Answer 2 · 2020-02-27T20:28:00

Well the problem is obviously that there might be more than one manager per employee.

So either you have to decide for a main manager for every employee which will need some changes to the database.
Or you have denormalize the query and show all managers in row. If you comma separate them it will look similar to this:

SQL

SELECT  ED.RefEmpID AS [EmpID]
       ,ED.EmpFirstName
       ,ED.EmpMiddleName
       ,ED.EmpLastName
       ,EMG.RefEMGID AS [ManagerID]
       ,COALESCE(
            STUFF
                (
                  (
                    SELECT  ', ' + EMG.RefEMGID
                      FROM  EmployeeManager EMG
                      WHERE ELC.RefEmpID = ED.RefEmpID
                      FOR XML PATH('')
                  ), 1, 2, N''
                )
            , N'') AS ManagerIDs
       ,ELC.RefELCID AS [ELCID]
       ,ELC.CashedDate
       ,ELC.CashedCount
       ,ELC.RefCalendarPeriodID AS [APID]
       ,ELC.PeriodStartDate
       ,ELC.PeriodEndDate
       ,LTY.RefLeaveTypeID AS [LTYID]
       ,LTY.LeaveTypeName
FROM    EmployeeLeaveCashed ELC
LEFT JOIN CalendarPeriod CP ON ELC.RefCalendarPeriod = CP.RefCalendarPeriod
LEFT JOIN LeaveType LTY ON ELC.RefLeaveTypeID = LTY.RefLeaveTypeID
LEFT JOIN Employee ED ON ELC.RefEmpID = ED.RefEmpID

Adjust as necessary.

Stefan_Lang · Answer 3 · 2020-02-27T20:33:00

You've got what you asked for: the Select fields contain fields from the EmployeeManager and from the Employee tables. Of course you will have multiple entries per Employee!

If you don't actually need the EMG fields, drop that join and the accompanying fields. If you only want to see data from one specific Manager, then you need to specify a condition to select the one manager that you want. Otherwise your output is what you asked for.

P.S.: Like Originalgriff said in solution 2, you probably modeled your manager relation upside down. Changing that should resolve the issue.

Sql join return only one row from left table

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Sql join return only one row from left table

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us