Help in Full Join in Query of Employee

Kenny Gua 411 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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113.7K 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
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful