Re: SQL: how to join two files using field depending on values

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!