php - 如何从mysql中的parent_id获取值？

Question

我有一个示例数据

game(id, name)
      1 | Avatar
      2 | Angry Bids
brand(id, parent_id, name)
      1 | 0 | Apple
      2 | 0 | Samsung
      3 | 1 | Iphone 4
      4 | 2 | Galaxy Tab
game_brand(game_id, brand_id)
              1   | 4
              2   | 3

并查询:

SELECT game.name, 
       game_brand.brand_id, 
       (SELECT brand.parent_id 
        FROM   brand AS brand 
        WHERE  brand.brand_id = game_brand.brand_id) AS brand_father_id 
FROM   game AS game 
       LEFT JOIN game_brand AS game_brand 
              ON game_brand.game_id = game.id 
WHERE  game.id = 2 

和结果:

name       | brand_id |    brand_father_id
Angry Bids |    3     |       1

如何获取brand_id、brand_father_id的值，eg:

game       | brand    | brand parent
Angry Bids | Iphone 4 | Apple 

Answer 1

SELECT game.name, child.name AS brand_name, parent.name AS brand_father_name
FROM game_brand INNER JOIN game ON game_brand.game_id = game.id
INNER JOIN brand AS child ON game_brand.brand_id = child.id
INNER JOIN brand AS parent ON child.parent_id = parent.id
WHERE game.id = 2

已更新

SELECT game.name, child.name AS brand_name, (SELECT name FROM brand AS parent WHERE child.parent_id = parent.id) AS brand_father_name
FROM game_brand INNER JOIN game ON game_brand.game_id = game.id
INNER JOIN brand AS child ON game_brand.brand_id = child.id
WHERE game.id = 2

再次更新

又一次刚刚注意到OP不仅想要父亲的名字，还想要 child 的名字(而不是他们的身份证)，所以更新了他们两个......