Help With Outer Joins [message #226806] |
Mon, 26 March 2007 10:39 |
queue18
Messages: 2 Registered: March 2007
|
Junior Member |
|
|
Hello.
I'm currently trying to rewrite some existing non-ANSI joins and have come across one that I'm not sure how to handle.
Here is a sample of the existing code:
SELECT receipts_payment_method.transaction_number,
receipts_detail.terms_amt,
receipts_detail.allowed_amt,
isNull(receipts_payment_method.check_number,'n/a') check_number,
isNull(receipts_payment_method.credit_card_type,'n/a') credit_card_type,
isNull(paymnt_method.payment_desc,'n/a') payment_desc,
isNull(cc_type.credit_card_desc,'n/a') credit_card_desc
FROM receipts,
receipts_detail,
receipts_payment_method,
paymnt_method,
cc_type
WHERE (receipts_payment_method.payment_id *= paymnt_method.payment_id) and (cc_type.credit_card_id =* receipts_payment_method.credit_card_type) and (receipts_payment_method.transaction_number = receipts.transaction_number);
I'm not sure how to rewrite the two joins since there are 3 tables involved (receipts_payment_method, paymnt_method, and cc_type).
Any help would be appreciated! Thanks in advance!
|
|
|
Re: Help With Outer Joins [message #226857 is a reply to message #226806] |
Mon, 26 March 2007 15:15 |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
It's not Oracle joins either, but if I understood it correctly then try:
SELECT receipts_payment_method.transaction_number,
receipts_detail.terms_amt,
receipts_detail.allowed_amt,
NVL(receipts_payment_method.check_number,'n/a') check_number,
NVL(receipts_payment_method.credit_card_type,'n/a') credit_card_type,
NVL(paymnt_method.payment_desc,'n/a') payment_desc,
NVL(cc_type.credit_card_desc,'n/a') credit_card_desc
FROM receipts
/* FIRST JOIN IS REGULAR ONE */
JOIN receipts_payment_method ON (receipts_payment_method.transaction_number = receipts.transaction_number)
LEFT OUTER JOIN paymnt_method ON (receipts_payment_method.payment_id = paymnt_method.payment_id)
LEFT OUTER JOIN cc_type ON (cc_type.credit_card_id = receipts_payment_method.credit_card_type)
However your source statement is missing a JOIN conditions to
receipts_detail table, so I didn't include it at all.
HTH.
Michael
|
|
|
|
|
|