CODEWITHSUNDEEP
t-sql
Pingpong
Pingpong

Reputation: 8009

display unique row from two tables

I have two tables (one for quarter one, one for quarter two), each of which contains employees who have bonus in that quarter. Every employee has a unique id in the company.

I want to get all employees who has bonus in either q1 or q2. No duplicate employee is needed. Both Id, and Amount are required.

Below is my solution, I want to find out if there is a better solution.

declare @q1 table (
EmployeeID int identity(1,1) primary key not null,
amount int
)


declare @q2 table (
EmployeeID int identity(1,1) primary key not null,
amount int
)


insert into @q1 
(amount)
select 1

insert into @q1 
(amount)
select 2

select * from @q1

insert into @q2
(amount)
select 1

insert into @q2 
(amount)
select 11

insert into @q2
(amount)
select 22

select * from @q2

My Solution:

;with both as
(
select EmployeeID
from @q1
union
select EmployeeID
from @q2
)
select a.EmployeeID, a.amount
from @q1 as a
where a.EmployeeID in (select EmployeeID from both)
union all
select b.EmployeeID, b.amount
from @q2 as b
where b.EmployeeID in (select EmployeeID from both) and b.EmployeeID NOT in (select EmployeeID from @q1)
Result:

EmployeeID, Amount

1   1
2   2
3   22

Upvotes: 0

Views: 150

Answers (3)

pete
pete

Reputation: 25081

Try:

SELECT  DISTINCT q1.EmployeeID --- Same as q2.EmployeeID thanks to the join
        , q1.EmployeeName -- Not defined in OP source.
FROM    @q1 AS q1
        CROSS JOIN @q2 AS q2
WHERE   q1.amount IS NOT NULL
        OR q2.amount IS NOT NULL

Upvotes: 0

Andrew
Andrew

Reputation: 4624

SELECT EmployeeID, Name, SUM(amount) AS TotalBonus
FROM
(SELECT EmployeeID, Name, amount
from @q1
UNION ALL
SELECT EmployeeID, Name, amount
from @q2) AS all 
GROUP BY EmployeeID, Name

The subselect UNIONS both tables together. The GROUP BY gives you one row per employee and the SUM means that if someone got lucky in both qs then you get the total. I'm guessing that's the right thing for you.

Upvotes: 1

John Woo
John Woo

Reputation: 263703

try this one:

SELECT EmployeeID
FROM EmployeeList
WHERE EmployeeID IN
    (SELECT EmployeeID From QuarterOne  
        UNION
     SELECT EmployeeID From QuarterTwo)

OR by using JOIN

SELECT EmployeeID
FROM EmployeeList a INNER JOIN QuarterTwo b 
           ON a.EmployeeID = b.EmployeeID
     INNER JOIN QuarterTwo c 
           ON a.EmployeeID = c.EmployeeID

This will return all EmployeeID that has record in either quarter.

Upvotes: 0

Related Questions