CODEWITHSUNDEEP
mysql
user3684962
user3684962

Reputation: 1

joining two tables on multiple columns

i have two tables in mysql:

table1
id  |   name
=============
11  |   AAA
22  |   BBB
33  |   CCC

table2 
id  |   type1   |   type2   |   type3
======================================
1   |   22      |   11      |   33      
2   |   11      |   22      |           
3   |   33      |           |   11

Below is the MYSQL query Im using: 

SELECT table2.type1, table1.name
FROM table2, table1 
WHERE table2.type1=table1.id

or 

SELECT table2.type1, table1.name FROM table2
inner join table1 on table2.type1=table1.id

have this result:

type1   |   name
================
22      |   BBB
11      |   AAA
33      |   CCC

how can i have this:???

id  |   type1   |   name    |   type2   |   name    |   type3   |   name    
=========================================================================
1   |   22      |   BBB     |   11      |   AAA     |   33      |   CCC
2   |   11      |   AAA     |   22      |   BBB     |           |   
3   |   33      |   CCC     |           |           |   11      |   AAA

Upvotes: 0

Views: 46

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

You'll want to join to table1 multiple times, like so...

SELECT t2.type1, t1_1.name AS n1
     , t2.type2, t1_2.name AS n2
     , t2.type3, t1_3.name AS n3
FROM table2 AS t2
LEFT JOIN table1 AS t1_1 ON t2.type1=t1_1.id
LEFT JOIN table1 AS t1_2 ON t2.type2=t1_2.id
LEFT JOIN table1 AS t1_3 ON t2.type3=t1_3.id
;

Note that the aliases on table1 are needed (though they may be different than what I've used) to differentiate one join's fields from another's; similarly, the name fields need aliases in the results to prevent ambiguity.

Upvotes: 1

Related Questions