Hello ,
First I create three temporary table for explanation .
create table #tmp1
(
referenceno varchar(10),
statuscode varchar(20),
)
create table #tmp2
(
referenceno varchar(10),
statuscode varchar(20),
)
create table #tmp3
(
referenceno varchar(10),
statuscode varchar(20),
)
insert into #tmp1 values('123', 'pending')
insert into #tmp1 values('234', 'pending')
insert into #tmp1 values('345', 'pending')
insert into #tmp1 values('456', 'pending')
insert into #tmp1 values('567', 'pending')
insert into #tmp1 values('678', 'pending')
insert into #tmp2 values('345', 'going')
insert into #tmp2 values('456', 'going')
insert into #tmp2 values('567', 'going')
insert into #tmp3 values('456', 'done')
insert into #tmp3 values('567', 'done')
insert into #tmp3 values('678', 'done')
to get required OP , first make union all of those three tables .
and then use Row_Number with Partition by clause, to sequence them one by one by statuscode and lastly use Common Table Expression to get required OP .
;with CTE
as
(
select ROW_NUMBER() OVER (partition by referenceno ORDER BY statuscode) AS RowNumber , *
from
(
select * from #tmp1
union all
select * from #tmp2
union all
select * from #tmp3
)xx)
select referenceno , statuscode from cte where RowNumber=1
OP
referenceno statuscode
123 pending
234 pending
345 going
456 done
567 done
678 done
Here another solution that may help you without using CTE
First Select only those records from #tmp1 or first table which are not present in other two tables (#tmp2 and #tmp3)
again select only those records from #tmp2 or second table which are not present in
last table (#tmp3)
and lastly make union all between those records sets .
select referenceno , statuscode from #tmp1 where referenceno
not in
(
select referenceno from #tmp2
union all
select referenceno from #tmp3
)
union all
select referenceno , statuscode from #tmp2 where referenceno
not in
(
select referenceno from #tmp3
)
union all
select referenceno , statuscode from #tmp3
Here also OP is
referenceno statuscode
123 pending
234 pending
345 going
456 done
567 done
678 done
Thanks