Home » SQL & PL/SQL » SQL & PL/SQL » Help With Outer Joins
Help With Outer Joins [message #226806] Mon, 26 March 2007 10:39 Go to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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
Re: Help With Outer Joins [message #226976 is a reply to message #226806] Tue, 27 March 2007 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you come to an Oracle forum with SQLServer/Sybase questions?
Are we really that much better than SQLServer-coders?
Re: Help With Outer Joins [message #227021 is a reply to message #226806] Tue, 27 March 2007 02:49 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Frank,

Instead of thinking that the poster can not differentiate betwen different databases, I take it as compliment (yes, we are much better).

Michael
Re: Help With Outer Joins [message #227203 is a reply to message #226806] Tue, 27 March 2007 09:12 Go to previous message
queue18
Messages: 2
Registered: March 2007
Junior Member
Frank - Michael is right. None of the "SQL Server-coders" seemed to know the answer, and the people here sounded like they knew what they were talking about! I figured I'd turn to the experts! Smile


Michael - that worked great! Thanks a bunch! Smile
Previous Topic: Script prob after changing from Oracle 9i to 10g R2 ORA-02021 ORA-30041
Next Topic: How to load data into two tables from Ms-excel file using script
Goto Forum:
  


Current Time: Sun Dec 11 02:29:17 CST 2016

Total time taken to generate the page: 0.12802 seconds