Hi Joel,

Kevin and Charles have given you what you need or at least got you close to
what you need.

I'd like to add a generalized comment.

JOINs are really only looking for a TRUE or FALSE comparison result. If
you can form a comparison in the ON clause of a JOIN, you can join on that
comparison. Those comparisons can be based on comparing two complex
expressions (calculations) if you want. Typically a comparison is based on
equality, but you can do <>, >, <, and many other types of comparisons as
well. Yes, CASE can be used in a JOIN.

That said, just because you can JOIN on just about anything, doesn't mean
doing so will always yield good performance.

Mike

date: Thu, 10 Dec 2015 19:27:00 +0000
from: "Stone, Joel" <Joel.Stone@xxxxxxxxxx>
subject: SQL: how to join two files using field depending on values

Is it possible to join two files in SQL as follows:

FileA
a.Cust#
a.Cust_type_Buyer_or_Seller value "B" or "S"



FileB
b.cust#_buyer example value 123
b.cust#_seller example value 789


Is it possible to join FileA to FileB where a.Cust# will join with
b.cust#_buyer ONLY when a.Cust_type_Buyer_or_Seller = "B", and also join
with b.cust#_seller ONLY when a.Cust_type_Buyer_or_Seller = "S" ?


Clear as mud?

Hoping this can be done with a CASE statement?

Or maybe with two separate queries?

Thanks in advance!



This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].