CODEWITHSUNDEEP
sqlviewsql-view
Pankaj Mishra
Pankaj Mishra

Reputation: 20348

How To create correct Join between two tables?

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

Upvotes: 0

Views: 37

Answers (3)

vathek
vathek

Reputation: 551

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

Upvotes: 0

void
void

Reputation: 7880

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

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 0

Related Questions