SQL Server 2005 - Help With Outer Joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server 2005 - Help With Outer Joins

Author

Topic

jlclark
Starting Member

1 Post

Posted - 2007-03-26 : 14:27:43

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) along with an "and".

Any help would be appreciated! Thanks in advance!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 15:17:44

You didn't have a condition in your query for joining receipts and receipts_detail, so I guessed at what it should be, but you should get he idea from this. I also changed your use of ISNULL to COALESCE since that is ANSI standard too.

SELECT receipts.transaction_number,
receipts_detail.terms_amt,
receipts_detail.allowed_amt,
coalesce(receipts_payment_method.check_number,'n/a') check_number,
coalesce(receipts_payment_method.credit_card_type,'n/a') credit_card_type,
coalesce(paymnt_method.payment_desc,'n/a') payment_desc,
coalesce(cc_type.credit_card_desc,'n/a') credit_card_desc
FROM receipts 
INNER JOIN receipts_detail ON receipts_detail.transaction_number = receipts.transaction_number
INNER JOIN receipts_payment_method ON receipts_payment_method.transaction_number = receipts.transaction_number
LEFT OUTER JOIN paymnt_method ON paymnt_method.payment_id = receipts_payment_method.payment_id
LEFT OUTER JOIN cc_type ON cc_type.credit_card_id = receipts_payment_method.credit_card_type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources