Re: join problem

Michael Ragsdale 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.
>
>

Back to relational databases 101.

Think for a moment about what you are asking the DB to do (logically). Ignoring conditions (WHERE clause) a JOIN is the cartesian product of all the tables in the FROM clause. An OUTER JOIN on one of those tables adds additional rows to the product with NULL for the attributes (columns) of that table.

So when you add a condition to the WHERE clause that looks for specific values like "c.status = 2" on the table that was the OUTER JOIN, you are filtering out those rows with nulls. This applies to all the tables related thru the outer join.

Think about it. Make a small example set:

CUSTOMER
CUID NAME

1     JOE
2     SAM
3     WIL

INVENTORY
CUID STATUS ITID

1     2        9
3     1        8

so "FROM customer, inventory"
logically implies the cartesian product with 6 rows

CUID NAME CUID STATUS ITID

1     JOE   1     2        9
2     SAM   1     2        9
3     WIL   1     2        9
1     JOE   3     1        8
2     SAM   3     1        8
3     WIL   3     1        8

then adding the outer join for INVENTORY adds three more rows

CUID NAME CUID STATUS ITID

1     JOE   1     2        9
2     SAM   1     2        9
3     WIL   1     2        9
1     JOE   3     1        8
2     SAM   3     1        8
3     WIL   3     1        8
1     JOE
2     SAM
3     WIL

Now you can think about which of these rows you want in your final result set. If you add any condition to the where clause like:

inventory.status=2

you see now why the outerjoin rows went away.

If you think about what is LOGICALLY happening, you'll see a little easier how to form your query.

  HTH
    Ed

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700   --- 7295 Popham Place, Solon, OH 44139
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com