Skip to content
Advertisement

group and join two tables based on id?

I have a mysql table that looks something like this:

id | name
-----------
1  |  cola
2  |  pepsi
3  |  sprite

and other table:

costumer | buy1 | buy2
----------------------
Jhon     | 2    | 3
Alice    | 1    | 3
Tony     | 3    | 2

I want to join the two tables and generate

 costumer | buy1 | buy2
----------------------
Jhon     | Pepsi | Sprite
Alice    | Cola  | Sprite
Tony     | Sprite| Pepsi

Advertisement

Answer

 SELECT C.costumer,REF.NAME,REF2.NAME
 FROM OTHER_TABLE AS C
 JOIN TABLE_SOMETHING_LIKE_THIS AS REF ON C.BUY1=REF.ID
 JOIN TABLE_SOMETHING_LIKE_THIS AS REF2 ON C.BUY2=REF2.ID
9 People found this is helpful
Advertisement