CODEWITHSUNDEEP
sqlsql-server
west
west

Reputation: 384

Join two tables to get matching records alongside with unmatched records

For example I have two tables

DECLARE @Employee TABLE( 
   [EmployeeID] INT NOT NULL, 
   [FirstName] VARCHAR(250) NOT NULL, 
   [LastName] VARCHAR(250) NOT NULL,
   [Value] INT NOT NULL,
   [ExpenditureID] INT NOT NULL
);

DECLARE @Expenditure TABLE( 
   [ExpenditureID] INT NOT NULL, 
   [Type] VARCHAR(250) NOT NULL   
);


INSERT @Expenditure ([ExpenditureID], [Type])
VALUES (1, N'Salary' ),
(2, N'Bonus')

INSERT @Employee([EmployeeID], [FirstName], [LastName], [Value], [ExpenditureID]) 
VALUES (1, N'Orlando', N'Gee', 1500, 1 ),
(2, N'Keith', N'Harris', 1000, 1),
(3, N'Keith', N'Harris', 700, 2),
(4, N'Donna', N'Carreras',2000, 1 ),
(5, N'Janet', N'Gates', 900, 1 )

I want to get result like this

FirstName | LastName | Value | Type
-----------------------------------
Orlando     Gee        0       Bonus
Orlando     Gee        1500    Salary
Keith       Harris     700     Bonus
Keith       Harris     1000    Salary
Donna       Carreras   0       Bonus
Donna       Carreras   2000    Salary
Janet       Gates      0       Bonus
Janet       Gates      900     Salary

I wrote query which returns same result

SELECT p.FirstName, p.LastName, SUM(p.Value) AS Value, p.Type FROM (
    SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.Value, exp.Type FROM @Employee AS emp
        INNER JOIN @Expenditure AS exp ON exp.ExpenditureID = emp.ExpenditureID 
    UNION ALL
    SELECT emp.EmployeeID, emp.FirstName, emp.LastName, 0, exp.Type FROM @Employee AS emp
        CROSS JOIN @Expenditure AS exp
    WHERE exp.ExpenditureID <> emp.ExpenditureID
) AS p
GROUP BY p.FirstName, p.LastName, p.Type

But I don't like this. Is there another solution to get result with more efficiently?

Upvotes: 1

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Use a cross join to generate the rows and a left join to bring in the values:

select n.*, e.type, coalesce(em.value, 0) as value
from (select distinct firstname, lastname from employee) n cross join
     expenditure e left join
     employee em
     on em.firstname = n.firstname and em.lastname = n.lastname and
        em.ExpenditureID = e.ExpenditureID;

Aggregation does not seem necessary but might be if you have multiple rows that need to be s summed together. Your sample data has no such examples.

Here is a db<>fiddle.

Upvotes: 2

Related Questions