Share via

Help in Full Join in Query of Employee

Kenny Gua 431 Reputation points
2021-09-03T16:29:50.007+00:00

Hello, I am not getting the expected result. Missing Ahsan Khan and Samad Khan in the result. Both have no perc amount so it should be blank in the result.
drop table #a,#b
Create table #a (Employee char(10), Bid char(2), ELastName char(10), Efname char(10))
Insert into #a values ('11111','1','Ahsan','Khan')
Insert into #a values ('11111','2','Samad','Khan')
Insert into #a values ('11111','3','Neelam','Khan')

Create table #b (Employee char(10), BT char(4), ELastName char(10), Efname char(10), perc int)
Insert into #b values ('11111','LLL','Neelam','Khan', 100)
Insert into #b values ('11111','DDD','Neelam','Khan',100)

Select a.Employee, a.Bid,a.ELastName,a.Efname,b.BT, b.Perc from #a a left join #b b
on a.Employee=b.Employee where a.Employee='11111' and a.Efname=b.Efname and a.ELastName=b.ELastName

Expected result:
Employee Bid ELastName Efname BT Perc
11111 1 Ahsan Khan
11111 2 Samad Khan
11111 3 Neelam Khan LLL 100
11111 3 Neelam Khan DDD 100

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

Viorel 126.9K Reputation points
2021-09-03T16:45:18.737+00:00

This query seems to work: 

select 
    coalesce(a.Employee, b.Employee) as Employee,
    a.Bid,
    u.ELastName,
    u.Efname,
    b.BT,
    b.perc
from #b b
right join (
    select ELastName, Efname
    from #a
    union 
    select ELastName, Efname
    from #b
    ) u on u.ELastName = b.ELastName and u.Efname = b.Efname
left join #a a on a.ELastName = u.ELastName and a.Efname = u.Efname
order by Bid

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.