Re: Conditional Join

On Nov 21, 8:40 pm, NitsR <nitinv..._at_gmail.com> wrote:
> Hello All
>
> I have a Customer Master table and have a child table for multiple
> address of 1 customer. In the Address table have a column for ActiveYN
> which is 'Y' for only one record. I want to get a view with the
> ActiveYN = 'Y' address by joining these 2 tables. And also need a
> outer join so even if there is no address for the customer then it
> will still get the customer master record
>
> I tried this
>
> Select a.CustomerCode,a.CustomerName,b.CustAddress
> From CustMast a, CustAddrMast b
> where Custmast.CustomerId = custaddrmast.CustomerId(+) and
> Customeraddrmast.ActiveYN = 'Y';
>
> but this results only in the matching records.
>
> Any help ?
> Thanks.

You don't say anything about what version of oracle. WIth many of the more recent versions you can do outer joins with regular syntax instead of the oracle "+" coding but of course if you use the oracle style coding you need to get the correct side for the outer join you want.

Sometimes I have found it handy to do the outer join then turn it into an inline view by sticking parens around it aka "( select statment with outer join coded one way or another )" before doing other manipulations. Received on Wed Nov 21 2007 - 21:20:02 CST