Re: Oracle SQL outer join problem

In article <87pd4p$l5p$1_at_nnrp1.deja.com>,   rplj_at_hotmail.com wrote:
Ruy, to start, your outer join did work, but then your where clause filtered the stock out. This is because the columns returned on an outer join with no match are NULL. If you account for the nulls, your query should work. Please see below, I've inserted the changes.

> Hello,
>
> I have an Oracle8i Enterprise database containing some tables:
>
> Product : Products list;
> Stock : Product stocks by filial;
> Group : Products have group codes from this table;
> Price : Product price list by product and filial;
>
> I've tried to join these guys together by using the following
> statement:
>
> select p.code,
> p.description as PrdDescr,
> g.description as GrpDescr,
> s.available,
> s.reserved,
> pr.price1,
> pr.price2
> from product p, stock s, group g, price pr
> where p.group_code = g.code
> and p.code = s.product_code(+)
> and p.code = pr.product_code(+)
> and s.filial_code = pr.filial_code
===> here the filial_code may be null, depending on the above join. ===> change the above statement to read   and (s.filial_code IS NULL OR pr.filial_code IS NULL

         OR s.filial_code = pr.filial_code)
> and s.filial_code = 1;

===> change the above statement to read, again accounting for possible nulls.
  and NVL(s.filial_code,1) = 1;
>
> See that it's in fact an Oracle outer join, because I need ALL
> products, even the ones without stocks or price, to be included
> in the result set.
>
> But the select is just returning products having stocks and price.
> Does anybody can help me to fix it?
>
> Thanks in advance,
>
> Ruy Ponce de León Jr.

HTH
    James

• Life is complex; it has real and imaginary parts.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 08 2000 - 15:56:33 CST