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

On 10-Dec-2015 13:27 -0600, Stone, Joel wrote:

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?

Sure:

select ...
from FileA as a
JOIN /* of appropriate type */
FileB as b
on a.cust# = case a.Cust_type_Buyer_or_Seller
when 'B' then b.cust#_buyer
when 'S' then b.cust#_seller
/* else null; gives no match */
end

Or just alternate predicate logic:

select ...
from FileA as a
JOIN /* of appropriate type */
FileB as b
on ( ( a.Cust_type_Buyer_or_Seller = 'B'
and a.cust# = b.cust#_buyer )
or ( a.Cust_type_Buyer_or_Seller = 'S'
and a.cust# = b.cust#_seller )
)

Or maybe with two separate queries?

Or nearly as much:

select ...
from ( select *
from FileA
where Cust_type_Buyer_or_Seller = 'B'
) as buyers
JOIN /* of appropriate type */
FileB as b
on buyers.cust# = b.cust#_buyer
union all
select ...
from ( select *
from FileA
where Cust_type_Buyer_or_Seller = 'S'
) as sellers
JOIN /* of appropriate type */
FileB as b
on sellers.cust# = b.cust#_seller