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


This thread ...

Replies:

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].