Group and join two tables based on id?

Question

I have a mysql table that looks something like this:

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

and another table:

customer | buy1 | buy2
---------+------+-----
Jhon     | 2    | 3
Alice    | 1    | 3
Tony     | 3    | 2

I want to join the two tables and generate

customer | buy1  | buy2
---------+-------+--------
Jhon     | Pepsi | Sprite
Alice    | Cola  | Sprite
Tony     | Sprite| Pepsi

Answer 1

You can write subqueries in a select list like this:

select 
    customer,
    (select name from tbl1 where id = buy1) buy1,
    (select name from tbl1 where id = buy2) buy2
from 
    tbl2;