alternatively, the same idea with "join" syntax
select ORDER_NO
, SOLD_TO_CUST_ID
, BILL_TO_CUST_ID
, ORDER_GRP
from PS_ORD_HEADER
left outer
join PS_CUSTOMER soldtocust
on SOLD_TO_CUST_ID = soldtocust.CUST_ID
left outer
join PS_CUSTOMER billtocust
on BILL_TO_CUST_ID = billtocust.CUST_ID
where ORDER_NO = 'OE-1062283'
note that i have coded left outer joins instead of inner
inner joins are what you get with "table list" syntax, e.g.
FROM A,B WHERE A.PK=B.FK
join syntax lets you say either left outer or inner (or even right outer, if
you feel like it, but i never do) with the same "on" clause in each case --
this makes it easy to switch back and forth if necessary (which is also why
i put left outer on its own line)
so if you really want inner joins, just change the words "left outer" to
"inner"
the trouble with "table list" syntax is that you cannot do an outer join
with it
[ everybody with me so far? i'm not sure i'm explaining this terribly
well ]
anyhow, coding left outer when you would normally expect an inner join is
what i call "defensive sql"
suppose that, in fact, each order has a matching, existing customer for both
the sold to and bill to customer ids
in that case, no harm is done, the correct records are returned, and you'd
never notice a difference between the left outer and inner joins
but if you code an inner join, and either one of those customer ids doesn't
match, suddenly that order drops out of the result set
remember, inner joins have to match all tables, or else the result set does
not contain that (partial) row, whereas with an outer join, you at least
know you are going to get every row from the left (first) table, that
satisfies the WHERE clause
this means the WHERE clause is also "cleaner" in that it puts the focus on
qualifying the rows rather than matching keys
[ how'm i doin? ]
the only time i would blithely use an inner join in a scenario like this one
is when there is relational integrity defined via foreign keys, such that it
would be impossible for an order to be inserted into the table without
matching customer rows on both the sold to and bill to foreign keys
since not everybody declares relational integrity constraints (and some
databases *cough*mysql*cough* don't even support it), you end up with lots
of application code designed to achieve the same objective (additional
queries before the insert to ensure existence, etc.) and we all know how
vulnerable application code can be to lapses of rigour
thus, defensive sql
helps in debugging apps
;o)
i would like to thank todd for the very kind words, and hope that i have
given you all some insight into how truly simple and straightforward this
stuff is
i'm also available for personal consultation if you fly me out to your shop
and let me crash on your couch overnight
;o)
rudy
____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED]
Send Your Posts To: [EMAIL PROTECTED]
To change subscription settings to the wdvltalk digest version:
http://wdvl.internet.com/WDVL/Forum/#sub
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
