Re: join problem

As to your last comment: .AFAIK, The (+) will only affect that specific criterion it is used in and not any that follow... You asked for a specific value of c.status IN ADDITION TO the c.i_cuid (+) = b.cuid outer join one.. so the rows were not returned that failed to satisfy that criterion...

To get the results you want you may want to use subqueries and EXISTS or NOT EXISTS operators.. Haven't tried to do it, however....

Michael Ragsdale <mragsdal_at_utk.edu> wrote:

>Thanks, that worked. Well, for that part anyway. However, the plot
>thickens. I was trying to debug my script one part at a time - I'm sorry, I
>guess I should have quoted it all the first time. Here is the whole
>snippet...at least all that deals with this inventory part....
>
>SELECT DISTINCT a.id
> b.name
> c.number
>FROM appeal a,
> customer b,
> inventory c,
> inventorytype d
>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 or c.status is null) AND
> d.itid = c.i_itid AND
> d.it_isid = c.i_isid;
>
>I need only those inventory items that are the current inventory series for
>that type. So, I join with the inventorytype table using the type id - all
>inventory items have a related type. Then I also join the same two tables
>using the inventory "series" id. Both of these tables have foreign keys to
>the series table, so why not compare them to each other without having to get
>the actual series table involved? The inventory table will always have a
>series, but that particular series may not be the current series in the
>inventorytype table - there is only one current series for each type. I
>can't see making outer joins or looking for nulls here because nulls
>shouldn't exist and outer joins would defeat the purpose of these joins in
>the first place.
>
>In brief, I only want the inventory to print if it is status 2 AND the
>current series, but I want the customer to print regardless. In the case
>above, the customer is not printing if his inventory item is from a
>non-current series.
>
>I'm still confused about the outer join. Since all of the criteria after the
>outer join line deals with the inventory side of that join, then why would
>any of that criteria affect whether or not the customer was actually
>printed. I thought the (+) filled in the missing gaps, so to speak.
>
>
>TurkBear wrote:
>
>> 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.
Received on Fri Nov 15 2002 - 11:55:15 CST