Re: join problem

Try adding a IS NULL to the last criteria:

AND (c.status = 2 or c_status is null);

That should handle those where no c.status exists.

Michael Ragsdale <mragsdal_at_utk.edu> wrote:

>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
Received on Fri Nov 15 2002 - 10:20:11 CST