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

Think this is what you are asking for...

select *
from fileA A
join fileB B
on (a.cust# = b.cust#_buyer and a.cust_buyer_or_seller = 'B')
or (a.cust# = b.cust#_seller and a.cust_buyer_or_seller = 'S')


Note that you could also uses UNION (cleaner but probably slower)
select *
from fileA A
join fileB B
on a.cust# = b.cust#_buyer
where a.cust_buyer_or_seller = 'B')
UNION ALL
select *
from fileA A
join fileB B
on a.cust# = b.cust#_seller
where a.cust_buyer_or_seller = 'S')

Lastly, not the same view you asked for but another way to look at the data
given that you can actually join to a file multiple times:

select *
from fileB B
join fileA AB on ab.cust# = b.cust#_buyer and ab.cust_buyer_or_seller
= 'B'
join fileA AS on as.cust# = b.cust#_buyer and as.cust_buyer_or_seller
= 'S'


Charles




On Thu, Dec 10, 2015 at 2:27 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> 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?

Or maybe with two separate queries?

Thanks in advance!



______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.