Re: join problem

Michael Ragsdale <mragsdal_at_utk.edu> wrote in message news:<3DD517E9.2484A727_at_utk.edu>...
> I have the following code:
>
> SELECT DISTINCT a.id
> b.name
> c.number
> FROM appeal a,
> customer b,
> inventory c
> WHERE TO_CHAR(a.insertdate, 'DD-MON-RR') = '14-NOV-02' AND
> b.cuid = a.ah_cuid AND
> c.i_cuid (+) = b.cuid;
>
> This works just fine and prints out the name of the customer for all
> that meet the date criteria regardless of whether or not there is a
> corresponding entry in the inventory table thanks to the outer join. It
> is also printing the inventory number IF it is present.
>
> However, when I add something to it like...
> AND c.status = 2;
> ...then it only prints out the customers where they have an inventory
> item with status of 2. I thought that the outer join was taking care of
> it when there was no inventory item for that customer. If the inventory
> item is not status 2, I don't want it to print, but I still want the
> customer name to print based on the insert date. What am I doing wrong
> with this join?
>
> -Mike

If you want to use an outer join for some table then you should specify "(+)" with every condition where this table is used. In your case it would be :

WHERE TO_CHAR(a.insertdate, 'DD-MON-RR') = '14-NOV-02' AND

       b.cuid = a.ah_cuid AND
       c.i_cuid (+) = b.cuid and
       c.status(+)=2;

HTH,
Igor Izvekov. Received on Fri Nov 15 2002 - 14:29:24 CST