Re: join problem

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:20:36 CST