CODEWITHSUNDEEP
sqlpostgresql
Kuczi
Kuczi

Reputation: 387

Selecting values from 2 columns that both are IDs of another tables rows

Let's say i have:

Table1:

ID    Name
1     Ann
2     Mike
3     Stan  
4     Kyle

Table2:

Pair ID    Person1ID    Person2ID
1          1            2
2          3            4

I want to select pairs, but with names instead of IDs, so this would be the output:

1    Ann    Mike
2    Stan   Kyle

I imagine a simple:

inner join Table1 on Table1.ID=Table2.Person1ID

won't work, because I want both of them, not only one.

I'm pretty new to SQL so i'm sorry if there is a simple answer.

Upvotes: 0

Views: 38

Answers (2)

Ali Azam
Ali Azam

Reputation: 2115

You can join sub query like below:

Select p.PairID, p.Name, q.Name
from 
    (Select t2.PairID, t1.Name
     from Table1 t1 inner join Table2 t2
     on t1.ID = t2.Person1ID) p
inner join   
    (Select t2.PairID, t1.Name
     from Table1 t1 inner join Table2 t2
     on t1.ID = t2.Person2ID) q
on p.PairID = q.PairID

See the result here in the demo.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

You have to join twice and use alias to diference the tables

SELECT t2.PairID, A.Name, B.Name
FROM Table2 t2
JOIN Table1 A
  ON t2.Person1ID = A.ID
JOIN Table1 B
  ON t2.Person2ID = B.ID

Upvotes: 4

Related Questions