Oracle outer join help

[bubu2006]

select a,b,c
from
mytab1 a full outer join
(select e,f,g
from s,b
where
s.w=b.w)m
on a.col1=m.e
mytab1 l left out join
(select ...
from ..
where..)k
on l.a=k.a

can I do this? This script is working fine if I am using union all. But I want to use one sql query & full outer join & left outer join.


I have tried this but getting more rows.

select a,b,c
from
mytab1 a full outer join
(select e,f,g
from s,b
where
s.w=b.w)m
on a.col1=m.e
left out join
(select ...
from ..
where..)k
on a.a=k.a


I would appreciiate your help.

I am using Oracle 10.

 

[SantaMufasa]

BuBu,

I (and others here) are happy to help, but to ensure that we offer code that meets your needs, I like to actually test the code (versus give an untested, "Ivory Tower" answer).

If you are willing to post code that CREATEs tables, their columns, and INSERTs to provide a few representative rows of data, then I'm certain we can give you an appropriate response.

Be sure to also describe what output you would like to see from the sample rows of INSERTed test data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

The principle certainly works:

create table tab1 (a number);
create table tab2 (a number, b number);
create table tab3 (b number);

insert into tab1 values (5);
insert into tab1 values (1);
insert into tab2 values (1, 2);
insert into tab2 values (2,3);
insert into tab2 values (3,4);
insert into tab3 values (3);

select t1.a, t2.b, t3.b
from tab1 t1 full outer join tab2 t2
on t1.a = t2.a
left outer join tab3 t3
on t3.b = t2.b
         A          B          B
---------- ---------- ----------
                    3          3
         5
         1          2
                    4

 

[bubu2006]

Dagon,

As a matter of fact I already did that yesterday but it's not working.

bubu

 

[bubu2006]

I want someting like this

select t1.a, t2.b, t3.b
from tab1 t1 full outer join tab2 t2
on t1.a = t2.a
left outer join tab3 t3
on t3.b = t1.a


guess t3.b=t1.a. Is it possible?

 

[Dagon]

Have you tried it ? I get some data:

 1  select t1.a, t2.b, t3.b
  2  from tab1 t1 full outer join tab2 t2
  3  on t1.a = t2.a
  4  left outer join tab3 t3
  5* on t3.b = t1.a
SQL> 
SQL> /

         A          B          B
---------- ---------- ----------
                    4
                    3
         5
         1          2