SQL Server join using multiple columns

Question

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

What's your expectation for multiple rows with the same One.A and Two.D values? Do you want to see duplicates? Have them grouped together? — Fuck StackOverflow
– Fuck StackOverflow, Commented Mar 13, 2014 at 17:42

Daniel Sparing · Accepted Answer · 2014-03-13 17:47:35Z

3

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;

edited Mar 13, 2014 at 17:47

answered Mar 13, 2014 at 17:42

Daniel Sparing

2,18316 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

unknown · Accepted Answer · 2014-03-13 17:52:36Z

0

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;

answered Mar 13, 2014 at 17:52

unknown

5,04510 gold badges49 silver badges62 bronze badges

4 Comments

Daniel Sparing Over a year ago

The problem with this is that you get multiple rows if there are multiple matches, say C1 in one row is the same as C2 in another row.

unknown Over a year ago

I don't think so. See sqlfiddle.com/#!2/ddc344/1, this is just an example. I know it is not suitable. check out result of that query.

user3416602 Over a year ago

@unknown Thanks, but your query returns multiple rows when more than one of C1/C2/C3 matches. I want result of first match not multiple rows.

unknown Over a year ago

Do you have SQL Fiddle sample template for query execution ? So that I can try with your data.

Collectives™ on Stack Overflow

SQL Server join using multiple columns

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related