FULL OUTER JOIN
-
Hi,
need result for a full outer join on 4 tables with 1 identical id.
select
COALESCE(t1.id,
t2.id,
t3.id,
t4.id
) id
from t1 full outer join t2 on t1.id on t2.id
full outer join t3 on t2.id = t3.id
full outer join t3 on t3.id = t4.id
But this will not give me each id existing uniqely.
t1 (1,2,3)
t2(3,4,5)
t3(1,7,8)
t4(3,4,9)
result needed:
1
2
3
4
5
7
8
9
Thanks,
Serge
-
Try this:
/*
t1 (1,2,3)
t2(3,4,5)
t3(1,7,8)
t4(3,4,9)
*/
create table dbo.t1 (
id int
)
create table dbo.t2 (
id int
)
create table dbo.t3 (
id int
)
create table dbo.t4 (
id int
)
go
insert into dbo.t1 values (1)
insert into dbo.t1 values (2)
insert into dbo.t1 values (3)
insert into dbo.t1 values (3)
insert into dbo.t1 values (4)
insert into dbo.t1 values (5)
insert into dbo.t1 values (1)
insert into dbo.t1 values (7)
insert into dbo.t1 values (8)
insert into dbo.t1 values (3)
insert into dbo.t1 values (4)
insert into dbo.t1 values (9)
go
select
t1.id
from
dbo.t1
union
select
t2.id
from
dbo.t2
union
select
t3.id
from
dbo.t3
union
select
t4.id
from
dbo.t4
order by
t1.id
go
drop table dbo.t1
drop table dbo.t2
drop table dbo.t3
drop table dbo.t4
go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply