CODEWITHSUNDEEP
sqldatabaseoracle-database
user1412040
user1412040

Reputation: 23

Right join on non-joinable columns

My question is simple, suppose i am joining between two tables and one of them is a right join

select e.name, nvl(e.mobileNumber, c.secondaryNumber)
from T_employee e, t_contact c
where e.fieldOfficeCode = 10
and e.id = c.id (+)
and c.status = 1001

now if I remove the last line of the query it would do the right join correctly, but once the last line is present I will not get any reslut. Any idea on how to resolve this. Now the query at hand is relatively complex and spans 5 tables but the sample snippet will simplify the actual problem

regards,

Upvotes: 1

Views: 258

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132620

This is what you need:

and c.status (+) = 1001

Or in modern syntax:

select e.name, nvl(e.mobileNumber, c.secondaryNumber)
from T_employee e
left outer join t_contact c
  on e.id = c.id
  and c.status = 1001
where e.fieldOfficeCode = 10

Your query was turning the outer join back into an inner join by only selecting joined records where c.status= 1001 - which excludes all no-match rows where it is null.

Upvotes: 3

Madhivanan
Madhivanan

Reputation: 13700

Try this

select e.name, nvl(e.mobileNumber, c.secondaryNumber) 
from T_employee e right join  t_contact c 
on and e.id = c.id (+) and c.status = 1001 
where e.fieldOfficeCode = 10

Upvotes: 0

Related Questions