CODEWITHSUNDEEP
sqlsql-serverjoin
user3416602
user3416602

Reputation: 33

SQL Server join using multiple columns

I need help with joining two tables in following scenario

Table One
Col A
Col B
Col C1
Col C2
Col C3

Table Two
Col C
Col D

I need to join [One] with [Two] and get One.A and Two.D as my output.

Here is the join logic:

Join on [One].C1 = [Two].C if no match, I need to join [One].C2 = Two.C again if no match join with [One].C3 = [Two].C

Upvotes: 2

Views: 5117

Answers (2)

unknown
unknown

Reputation: 5017

Try this query :

SELECT 
one.A as oneA,
two.D as twoD
FROM 
One one
INNER JOIN Two two ON one.C1 = two.C 
OR one.C1 = two.C2 
OR one.C3 = two.C;

Upvotes: 0

Daniel Sparing
Daniel Sparing

Reputation: 2183

You need COALESCE():

SELECT DISTINCT
    COALESCE(One1.A, One2.A, One3.A) AS A,
    D
FROM
    Two
    LEFT JOIN One AS One1 ON
        Two.C = One1.C1
    LEFT JOIN One AS One2 ON
        Two.C = One2.C2
    LEFT JOIN One AS One3 ON
        Two.C = One3.C3;

Upvotes: 3

Related Questions