CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Tassadaque
Tassadaque

Reputation: 8199

SQL- Add Missing data in Left outer joing query

I have following data
Components

componentid  title
1            houseRent
2            medical
3            Travelling Allowance

empPayrollMaster

MasterID EmployeeID SalaryMonthID
1          101     1
2          102     1
3          103     1

empPayrollDetail

DetailID MasterID ComponentID amount
1        1         1           100
2        1         2           500
3        2        1            300
4        2        3            250
5        3        1            150
6        3        2            350
7        3        3            450

Required Output

EmployeeID MasterID ComponentID amount
     101      1        1           100
     101      1        2           500
     101      1        3           0
     102      2        1            300
     102      1        2           0
     102      2        3            250
     103      3        1            150
     103      3        2            350
     103      3        3            450

To get the required output if i do left outer join between components and empPayrollDetail I get null in EmployeeID and MasterID and amount Columns. How to modify left join to get the required output

Upvotes: 2

Views: 111

Answers (5)

Mujahid Ali
Mujahid Ali

Reputation: 21

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Upvotes: 0

Carsten Massmann
Carsten Massmann

Reputation: 28226

As you want the component amount for each employee in the master table you should use a insull(payrole_detail.amount,0) or, as @Turophile pointed out, the SQL standard function coalesce(payrole_detail.amount,0) for the amounts column.

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this way

select c.EmployeeID,d.MasterID,c.ComponentID,isnull(d.amount,0) as amount from (
select * from Components a
Cross join empPayrollMaster b) c 
left outer join empPayrollDetail d on d.componentid  =c.componentid

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this 

  select empPayrollMaster.EmployeeID,empPayrollMaster.MasterID,
  Components.componentid,isnull(empPayrollDetail.amount,0) 
  from empPayrollMaster 
  left join Components
  on  empPayrollMaster.EmployeeID is not null 
  left join empPayrollDetail 
  on empPayrollDetail.MasterID = empPayrollMaster.MasterID
  and empPayrollDetail.ComponentID = Components.componentid

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You need to do a CROSS JOIN on Components and empPayrollMaster to generate first all combination of employees and components. Then, do a LEFT JOIN on empPayrollDetail to achieve the result, using ISNULL(amount, 0) for NULL amounts.

SQL Fiddle

SELECT
    epm.EmployeeID,
    epm.MasterID,
    c.ComponentID,
    amount = ISNULL(epd.amount, 0)
FROM empPayrollMaster epm
CROSS JOIN Components c
LEFT JOIN empPayrollDetail epd
    ON epd.MasterID = epm.MasterID
    AND epd.ComponentID = c.ComponentID

Upvotes: 6

Related Questions