469,628 Members | 1,044 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,628 developers. It's quick & easy.

full outer join question

Hi

I have the following 4 tables and I need to do a fully outerjoin on them.

create table A (a number, b number, c char(10), primary key (a,b))
create table B (a number, b number, c char(10), primary key (a,b))
create table C (a number, b number, c char(10), priamry key (a,b))
create table D (a number, b number, c char(10), priamry key (a,b))

In oracle 9i, the following query returns correct results set, if I were to
join just 2 tables - A & B

select A.a, A.b, A.c, B.c
from A full outer join B on
((A.a = B.a) and (A.b = B.b))

How do I extend this query to do a 4-way join ? Do you think the following
query would work or is there a simpler way ?

select A.a, A.b, A.c, B.c, C.c, D.c
from
A full outer join B on
((A.a=B.a)) and (A.b = B.b))
full outer join C on
((A.a = C.a) and (A.B = C.b)) OR ((B.a = C.a) and (B.b = C.b))
full outer join D on
((A.a = D.a) and (A.b = D.b)) OR ((B.a = D.a) and ((B.b =
D.b )) OR ((C.a = D.a) and (C.b = D.b))
;
Jul 19 '05 #1
1 11414
I think it is imposible I sujest longer and not prety way:

(select A.a, A.b, A.c, B.c
from A ,B
where A.a = B.a(+)
union
select A.a, A.b, A.c, B.c
from A ,B
where A.a (+)= B.a )

union

(select A.a, A.b, A.c, C.c
from A ,C
where A.a = C.a(+)
union
select A.a, A.b, A.c, C.c
from A ,B
where A.a (+)= C.a )

and so on :)) I dont't know how else you can outer join 4 tables
without rethinking design


"Dave" <ad***@comcast.net> wrote in message news:<HHz0b.203937$uu5.36905@sccrnsc04>...
Hi

I have the following 4 tables and I need to do a fully outerjoin on them.

create table A (a number, b number, c char(10), primary key (a,b))
create table B (a number, b number, c char(10), primary key (a,b))
create table C (a number, b number, c char(10), priamry key (a,b))
create table D (a number, b number, c char(10), priamry key (a,b))

In oracle 9i, the following query returns correct results set, if I were to
join just 2 tables - A & B

select A.a, A.b, A.c, B.c
from A full outer join B on
((A.a = B.a) and (A.b = B.b))

How do I extend this query to do a 4-way join ? Do you think the following
query would work or is there a simpler way ?

select A.a, A.b, A.c, B.c, C.c, D.c
from
A full outer join B on
((A.a=B.a)) and (A.b = B.b))
full outer join C on
((A.a = C.a) and (A.B = C.b)) OR ((B.a = C.a) and (B.b = C.b))
full outer join D on
((A.a = D.a) and (A.b = D.b)) OR ((B.a = D.a) and ((B.b =
D.b )) OR ((C.a = D.a) and (C.b = D.b))
;

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by reneecccwest | last post: by
7 posts views Thread by alexcn | last post: by
2 posts views Thread by Lee | last post: by
1 post views Thread by Martijn van Oosterhout | last post: by
1 post views Thread by Dave | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.