OraFAQ Forum: SQL & PL/SQL » Help With Outer Joins

Home » SQL & PL/SQL » SQL & PL/SQL » Help With Outer Joins

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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!

Report message to a moderator

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

Report message to a moderator

Re: Help With Outer Joins [message #226976 is a reply to message #226806]

Tue, 27 March 2007 00:45

Frank
Messages: 7901
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?

Report message to a moderator

Re: Help With Outer Joins [message #227021 is a reply to message #226806]

Tue, 27 March 2007 02:49

michael_bialik
Messages: 621
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

Report message to a moderator

Re: Help With Outer Joins [message #227203 is a reply to message #226806]

Tue, 27 March 2007 09:12

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 08:02:46 CST 2024