How To create correct Join between two tables?

Question

I have two tables and I want to get the data from both tables.

CustomerDetails

Id Customer1 Customer2
1  1         2
2  2         1
3  1         3

CustomerName

Id Name
1  a
2  b
3  c

output should be

Id Customer1 Customer2
1   a         b
2   b         a
3   a         c

I tried with inner join but it only worked for one column and not for both.

How do i get this data from a sql query.

Please help me find this.

Thanks

have you tried with two column in ON clause with OR. what you tried ? — Brave Soul
– Brave Soul, Commented Mar 24, 2015 at 10:21

void · Accepted Answer · 2015-03-24 10:23:37Z

1

use 2 joins

select t1.id,t2.name customer1 ,t3.name customer2
from customerdetail t1 
join customername t2 on t1.customer1=t2.id
join customername t3 on t1.customer2=t3.id

answered Mar 24, 2015 at 10:23

void

7,9503 gold badges29 silver badges47 bronze badges

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

Comments

Giannis Paraskevopoulos · Accepted Answer · 2015-03-24 10:23:03Z

0

SELECT Id,
       CN1.Name AS Name1,
       CN2.Name AS Name2
FROM   CustomerDetails CD
       JOIN CustomerName AS CN1
            ON CD.Customer1 = CN1.ID
       JOIN CustomerName AS CN2
            ON CD.Customer2 = CN2.ID

I would do it with LEFT JOIN as it would be more safe. I do not know if you do have always values for both Customer1 and Customer2

answered Mar 24, 2015 at 10:23

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Comments

vathek · Accepted Answer · 2015-03-24 10:26:35Z

0

This should be work:

SELECT CD.Id,
   CN1.Customer1,
   CN2.Customer2
FROM   CustomerDetails CD
   JOIN CustomerName AS CN1
        ON CD.Customer1 = CN1.ID
   JOIN CustomerName AS CN2
        ON CD.Customer2 = CN2.ID

answered Mar 24, 2015 at 10:26

vathek

5512 silver badges9 bronze badges

Collectives™ on Stack Overflow

How To create correct Join between two tables?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related