CODEWITHSUNDEEP
sqlsql-serverdatabase
TomYumGuy
TomYumGuy

Reputation: 55

Determine which keys should 2 tables use to Join in SQL Server

I have the follwoing 2 Tables:

Table1:

User-ID    Country

1111    USA 
2222    Brazil
3331    USA
3332    Korea
3333    Korea
3333    USA
3333    France
4444    UK

Table2:

User-ID   Country  Region

1111      USA       NA
2222      Brazil    SA
3331      Korea     A
3332      Korea     A
3333      N/A       N/A
3330      France    E
4444      UK        E

I would like to join both table using the 'User-ID' as the common key with an exception.

What I want the result to be like is that when the User-ID is '3333' I would like the 'Country' to be the common key instead ONLY.

I have tried the following ncode which sounded logical per my understanding but did not return the desitred result:

Select T1.User-ID, T1.Country, Case When T1.User-ID ='3333' AND T1.Country = 
       T2.Country THEN T2.Region ELSE T2.Region END

From Table1 as T1 Left Outer Join
     Table2 as T2 ON T1.User-ID = T2.User-ID OR T1.Country = T2.Country

I have tried few other approaches buty with no succes, the results I will get will be the following:

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You seem to want an additional condition in the ON clause:

From Table1 T1 Left Outer Join
     Table2 T2
     ON T1.UserID = T2.UserID AND
        (T1.UserId <> 33333 OR T1.Country = T2.Country)

Upvotes: 1

Related Questions