Outer Join with condition doesn't work

[dynamicjourney2001]

Hi Guys,

Well Im new with oracle.. I have a query with outer join
eg.
select a.1,b.1
from a, b,c
where a= b (+) and
a=c and
b=1

It seems that oracle satisfies the b=1 by ignoring the join(a=b(+)).

Thanks in advance.
Have fun.

Dynamicjourney

 

[Bveoracle]

Could you rephrase your question and example. There are too many things to explain.

Is this your example ? :

select a.1,
b.1
from a,
b,
c
where a.1 = b.1 (+) and
a.1 = c.1 and
b.1 = 1

Post some data to show what you think goes wrong.

Gr. Bart.

 

[Turkbear]

Hi,
By placing a condition on b ( b=1) the outer join will not be used...Only those rows where b=1 will be selected, so no outer join would be involved since for b to be equal to 1, it must exist...

 

[dynamicjourney2001]

the join a= b(+) means that all rows in a that 'are not also' in b. and with the condition b=1, isn't it suppose to show all rows in a that have no b equivalent and all rows in a that are in b but b=1.

 

[jee]

Not exactly - The rows from a that have no match in b are treated as if the "b" columns for those rows are all NULLs, therefore they fail the b.1=1 condition.

 

[Beantree]

you can fget the behavior you want by making the criteria for b something like (b = 1 or b is null)

 

[Turkbear]

Hi,
Or you can use try 2 subquerys with a union :

select * from (select a.1,
       b.1
from a,
     b,
     c
where a.1 = b.1 (+) and
      a.1 = c.1  
 union
 select a.1,
       b.1
from a,
     b,
     c
where a.1 = b.1
and b.1 = 1)

Or something like that...

 

[T8000]

You can add a plus to the and clause: b (+) = 1