CODEWITHSUNDEEP
sql-server
sripriya
sripriya

Reputation: 131

How to compare two tables in SQL Server adding value to matched Employee

I have two tables in SQL Server, Table1 and Table2. Both tables have a column EmployeeName which should match almost both tables.

This is my sample data in Table 1 :

EmployeeName   Expenses   Bonus  overTime    
-----------------------------------------
John             198      3734     230
Ayesa              0      2384       0
Nimi             400       230       0
Michael            0       571       0
Alexander        212       240       0

Table 2 Employee Name almost has the same values - like this:

EmployeeName     Ermatch      Roth       
-----------------------------------
John               298         500
Ayesa                0         450
Nimi               400         250      
Michael              0         551

Now both tables which are the employee match both Table 1 and Table 2. Finally join the one Table like this 

 EmployeeName   Expenses   Bonus  overTime    Ermatch  Roth
 ----------------------------------------------------------
 John             198       3734    230         298    500
 Ayesa              0       2384      0           0    450
 Nimi             958        230      0         400    250
 Michael            0        571      0           0    551
 Alexander        212        240      0           0      0

Which are Employeename match both table Ermatch and Roth join Table 1. how to join both table? how to compare both Table Employeename . thanks in advance

Upvotes: 0

Views: 62

Answers (3)

hassan naeimi
hassan naeimi

Reputation: 119

also you can join both table with full join like below.it use when one Table has value but another Table hasn't this value .

;WITH Tab1( EmployeeName,Expenses,Bonus,overTime)
     AS (                         
     SELECT 'John'     ,    198   , 3734  ,   230 union all
     SELECT 'Ayesa'    ,      0   ,  2384 ,    0  union all
     SELECT 'NImi'     ,     400  ,  230  ,    0  union all
     SELECT 'Michael'  ,       0  ,   571 ,     0 union all
     SELECT 'Alexander',     212  ,   240 ,     0 
     )
     ,tab2( EmployeeName,Ermatch,Roth)
     AS
     (                      

     SELECT 'John'     ,298     ,500  Union all
     SELECT 'Ayesa'    , 0      ,450  Union all
     SELECT 'NImi'     , 400    ,250  Union all
     SELECT 'Michael'  , 0      ,551  union all
     SELECT 'Alex'  , 300      ,500
     )
     SELECT isnull(T1.EmployeeName,T2.EmployeeName) EmployeeName,
            ISNULL(T1.Expenses,0)Expenses,
            ISNULL(T1.Bonus,0)Bonus,
            ISNULL(T1.overTime,0)overTime,
            ISNULL(T2.Ermatch,0)Ermatch,
            ISNULL(T2.Roth,0)Roth 
        FROM Tab1 T1 full join tab2 t2
    ON t2.EmployeeName=T1.EmployeeName

Upvotes: 2

TriV
TriV

Reputation: 5148

If you want data in both two tables, I think what you need is FULL JOIN

DECLARE @Table1 as Table (EmployeeName varchar(20),   Expenses int,  Bonus  int ,overTime     int)

INSERT INTO @Table1
VALUES
  ('John',             198,     3734,    230),
  ('Ayesa',              0,     2384,      0),
  ('Nimi',             400,      230,      0),
  ('Michael',            0,      571,      0),
  ('Alexander',        212,      240,      0)

DECLARE @Table2 AS TABLE (EmployeeName varchar(20),   Ermatch   int,  Roth   int )
INSERT INTO @Table2
VALUES 
  ('John',               298,        500),
  ('Ayesa',                0,        450),
  ('Nimi',               400,        250),     
  ('Michael',              0,        551),
  ('NewUser',              0,        551)

SELECT  COALESCE (t.EmployeeName, t2.EmployeeName) AS  EmployeeName,
      ISNULL(t.Expenses,0) AS Expenses, 
      ISNULL(t.Bonus,0) AS Bonus, 
      ISNULL(t.overTime,0) AS overTime, 
      ISNULL(t2.Ermatch,0) AS Ermatch, 
      ISNULL(t2.Roth,0) AS Roth
FROM @Table1 t
FULL JOIN @Table2 t2 ON t.EmployeeName = t2.EmployeeName

Demo link: http://rextester.com/VKJT88878

Upvotes: 1

user7715598
user7715598

Reputation:

Join both table like below.This code might be usefull for your requirement

;WITH Tab1( EmployeeName,Expenses,Bonus,overTime)
     AS (                         
     SELECT 'John'     ,    198   , 3734  ,   230 union all
     SELECT 'Ayesa'    ,      0   ,  2384 ,    0  union all
     SELECT 'NImi'     ,     400  ,  230  ,    0  union all
     SELECT 'Michael'  ,       0  ,   571 ,     0 union all
     SELECT 'Alexander',     212  ,   240 ,     0 
     )
     ,tab2( EmployeeName,Ermatch,Roth)
     AS
     (                      

     SELECT 'John'     ,298     ,500  Union all
     SELECT 'Ayesa'    , 0      ,450  Union all
     SELECT 'NImi'     , 400    ,250  Union all
     SELECT 'Michael'  , 0      ,551
     )
     SELECT T1.EmployeeName,
            ISNULL(T1.Expenses,0)Expenses,
            ISNULL(T1.Bonus,0)Bonus,
            ISNULL(T1.overTime,0)overTime,
            ISNULL(T2.Ermatch,0)Ermatch,
            ISNULL(T2.Roth,0)Roth 
        FROM Tab1 T1 LEFT join tab2 t2
    ON t2.EmployeeName=T1.EmployeeName

Upvotes: 1

Related Questions