Inner join of 2 tables with the same ID

Question

I have a table(T1) in which 2 columns(X and Y) are id's. The name's of these corresponding id's are in another table (T2) with the column name.

Suppose I was only using X then, a simple Inner join would have solved my problem in getting the name.

Such as

Select T1.somedata,T1.somedata1,T2.name from T1
Inner Join T2 ON T1.X=T2.id

But,what if I want the name's to be resolved for the T1.Y also?, which name would the Inner Join resolve it to ??

Select T1.somedata,T1.somedata1,T2.name from T1
Inner Join T2 ON T1.X=T2.id
Inner Join T2 ON T1.Y=T2.id

The above query is wrong, I know. Can I get the names of those corresponding to both T1.Xand T1.Y with an INNER Join?

-Beginner

Solution 1

I suggest always add aliases to tables and columns. So you will be sure which data are selected.

select
    T1.somedata,
    T1.somedata1,
    T2X.name as XName,
    T2Y.name as YName
from T1 as T1
    inner join T2 as T2X on T2X.id = T1.X
    inner join T2 as T2Y on T2Y.id = T1.Y

Solution 2

This selects both names as separate columns:

Select T1.somedata,T1.somedata1,T2a.name, T2b.name 
from T1
Inner Join T2 as T2a ON T1.X=T2a.id 
Inner Join T2 as T2b ON T1.Y=T2b.id

The following would generate two records in the result set:

Select T1.somedata, T1.somedata1, T2.name
from T1
Inner Join T2 ON T1.X=T2.id Or T1.Y=T2.id

Solution 3

you need to join table T2 twice and supply aliases on the names to avoid ambiguity.

SELECT  a.*, 
        b.name as NameB, 
        c.name as NameC
FROM    T1 a
        INNER JOIN T2 b
            ON a.x = b.id
        INNER JOIN T2 c
            On a.y = c.id