CODEWITHSUNDEEP
mysql
user2168066
user2168066

Reputation: 635

Select statement joining on multiple columns

I have a table with 3 columns that each contain an ID to another table, I am trying to find a way to pull back the name for each of those ID's but am unsure how I can do this using just sql, and not having to run in a php loop. N1, N2, and N3 being the ID's of T2.

 T1                         T2
------------------          -----------------         
ID    N1   N2   N3          ID    Name
1     2    3    1           1     Steve
2     3    2    1           2     Bob
3     1    2    3           3     Dan

My desired output would be

T1.id    T1.N1     T1.N2    T1.N3
1        Bob       Dan      Steve

I'm not really sure where to begin on pulling the data this way. Any help is appreciated.

Upvotes: 0

Views: 41

Answers (2)

Star_Man
Star_Man

Reputation: 1091

Only three columns? Simply try this...

select id, 
      (select T2.Name from T2 where T2.id = N1) as N1, 
      (select T2.Name from T2 where T2.id = N2) as N2, 
      (select T2.Name from T2 where T2.id = N3) as N3
from T1

Upvotes: 1

flyingfox
flyingfox

Reputation: 13506

You can use LEFT JOIN for multiple times 

  SELECT t.ID,t21.Name,t22.Name,t23.Name 
     FROM table1 t 
     LEFT JOIN table2 t21 ON t21.ID=t.N1
     LEFT JOIN table2 t22 ON t22.ID=t.N2
     LEFT JOIN table2 t23 ON t23.ID=t.N3

Upvotes: 1

Related Questions