Saturday, November 28, 2015

Unmatched Records from Two tables by using CTE

Unmatched Records from Two tables by using CTE:
create table tab1
(
id int,
name varchar(20),
salary money
)

create table tab2
(
id int,
did int
)

insert into tab1 values(1,'A',2000),(2,'P',3000),(5,'J',2500),(8,'AK',5000)

insert into tab2 values(1,26),(2,50),(3,35),(4,56),(6,25)

Select * from tab1
Select * from tab2
table 1:                                                   table 2:
                                               




Required Result:



Query:
with e (id,id2,name,salary,did)
As
(
Select tab1.id,tab2.id,name,salary,did from tab1 
full outer join 
tab2 on tab1.id=tab2.id
)
Select * from e where
 id not in (select tab1.id from tab1 inner join tab2 on tab1.id=tab2.id) or
id2 not in (select tab1.id from tab1 inner join tab2 on tab1.id=tab2.id)

But if we want results like following:


Query:
with e (id,name,salary,did)
As
(
Select IsNull(tab1.id,tab2.id),name,salary,did from tab1 
full outer join 
tab2 on tab1.id=tab2.id
)

Select * from e where id not in (select tab1.id from tab1 inner join tab2 on tab1.id=tab2.id)

No comments:

Post a Comment