FULL OUTER JOIN with temp tables

Question

declare @Tax Table 
 (
   RowNumber int , 
   FirstName nvarchar(50),
   MiddleName  nvarchar(50),
   LastName nvarchar(50),
   SSN nvarchar(50),
   EmployeeCode nvarchar(50),
   TaxName nvarchar(50),
   Amount decimal(18,2),   
   GrossPay decimal(18,2),
   CompanyId int,
   EmployeeId int
 )

 INSERT into @Tax 


                select row_number() OVER (PARTITION BY E.EmployeeId order by E.EmployeeId ) as RowNumber,FirstName,MiddleName,LastName,SSN,EmployeeCode,TaxName,TC.Amount,dbo.[GrossIncomeCalculation](E.EmployeeId) as GrossPay
                ,E.CompanyId,E.EmployeeId
                from Employee    as E 
                cross apply (
                                select TT.*
                                from dbo.[StateFixedTaxesCalculation](dbo.[GrossIncomeCalculation](E.EmployeeId),E.EmployeeId,E.CompanyId,1006) as TT
                                where TT.EmployeeId=E.EmployeeId and E.CompanyId=1
                            ) as TC 






declare @Earnings Table 
 (
   RowNumber int , 
   EmployeeId int,  
   EarningDescription nvarchar(50),  
   Amount decimal(18,2)

 )


            INSERT into @Earnings
            SELECT RowNumber,EC.EmployeeId,EarningDescription,Amount FROM Employee as E
            CROSS APPLY 
            (
            select EC.*
            from dbo.[EarningCalculation](E.EmployeeId,E.CompanyId) as EC
            WHERE E.CompanyId=1

            )   as EC 


                declare @Deductions Table 
             (
               RowNumber int , 
               EmployeeId int,  
               DeductionDescription nvarchar(50),  
               Amount decimal(18,2)

             )      
            INSERT INTO @Deductions 
            SELECT RowNumber,EDD.EmployeeId,DeductionDescription,Amount FROM Employee as E
            CROSS apply (
                        select ED.*
                        from dbo.[DeductionCalculation](E.EmployeeId,E.CompanyId) as ED
                        WHERE E.CompanyId=1

            ) as EDD

I want to join these three tables data in a such way that its always product maximum number of rows for employee according to which table have max records and other table if have no rows or min to that table show null in column values.

I cannot use left join because i don't know which temp table may have more records.

here is image of my current data

current query

and my expected output should be look like this image

enter image description here

Seems like you should be using union, not joins. – athabaska Oct 30 '13 at 11:16 — athabaska, Oct 30 '13 at 11:16

score 2 · Accepted Answer · answered Oct 30 '13 at 11:32

You can still use a FULL JOIN, just use ISNULL on the second join condition:

SELECT  RowNumber = COALESCE(t.RowNumber, e.RowNumber, d.RowNumber),
        EmployeeID = COALESCE(t.EmployeeID, e.EmployeeID, d.EmployeeID),
        t.FirstName,
        t.MiddleName,
        t.LastName,
        t.SSN,
        t.EmployeeCode,
        t.TaxName,
        t.Amount,
        t.GrossPay,
        t.CompanyId,
        e.EarningDescription,
        EarningAmount = e.Amount,
        d.DeductionDescription,
        DeductionAmount = d.Amount
FROM    @Tax t
        FULL JOIN @Earnings e
            ON t.EmployeeID = e.EmployeeID
            AND t.RowNumber = e.RowNumber
        FULL JOIN @Deductions D
            ON d.EmployeeID = ISNULL(t.EmployeeID, e.EmployeeID)
            AND d.RowNumber = ISNULL(t.RowNumber, e.RowNumber);

Working example below (all columns other than those needed for joins are null though

DECLARE @Tax Table 
(
   RowNumber int , 
   FirstName nvarchar(50),
   MiddleName  nvarchar(50),
   LastName nvarchar(50),
   SSN nvarchar(50),
   EmployeeCode nvarchar(50),
   TaxName nvarchar(50),
   Amount decimal(18,2),   
   GrossPay decimal(18,2),
   CompanyId int,
   EmployeeId int
)
INSERT @Tax  (RowNumber, EmployeeID)
VALUES (1, 1), (2, 1), (3, 1), (4, 1);

DECLARE @Earnings TABLE
(
   RowNumber int , 
   EmployeeId int,  
   EarningDescription nvarchar(50),  
   Amount decimal(18,2)
)
INSERT @Earnings  (RowNumber, EmployeeID)
VALUES (1, 1), (2, 1);

DECLARE @Deductions TABLE 
(
    RowNumber int , 
    EmployeeId int,  
    DeductionDescription nvarchar(50),  
    Amount decimal(18,2)
) 
INSERT @Deductions  (RowNumber, EmployeeID)
VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1);  


SELECT  RowNumber = COALESCE(t.RowNumber, e.RowNumber, d.RowNumber),
        EmployeeID = COALESCE(t.EmployeeID, e.EmployeeID, d.EmployeeID),
        t.FirstName,
        t.MiddleName,
        t.LastName,
        t.SSN,
        t.EmployeeCode,
        t.TaxName,
        t.Amount,
        t.GrossPay,
        t.CompanyId,
        e.EarningDescription,
        EarningAmount = e.Amount,
        d.DeductionDescription,
        DeductionAmount = d.Amount
FROM    @Tax t
        FULL JOIN @Earnings e
            ON t.EmployeeID = e.EmployeeID
            AND t.RowNumber = e.RowNumber
        FULL JOIN @Deductions D
            ON d.EmployeeID = ISNULL(t.EmployeeID, e.EmployeeID)
            AND d.RowNumber = ISNULL(t.RowNumber, e.RowNumber);

score -1 · Answer 2 · answered Oct 30 '13 at 11:25

-1

I would do something like

SELECT UserId, Amount, 1 TableType FROM @Tax
UNION
SELECT UserId, Amount, 2 TableType FROM @Earnings
UNION
SELECT UserId, Amount, 3 TableType FROM @Deductions

Would be easier to interprete such structure.

answered Oct 30 '13 at 11:25

athabaska

455
3
22

FULL OUTER JOIN with temp tables

2 Answers2