Relate two mysql tables to data from the same column?

Question

I am creating a 2014 World Cup table.

I created two tables.

times (ID, nome, sigla, bandeira)
jogos (ID, fase, local, time1_id, time2_id, data)

I have a relatively simple problem, but I found no solution researching... I want to make a Select to bring me the name of time1 and of time2 in a consultation, replacing the ID.

SELECT `jogos`.*, `time`.`nome` FROM `jogos` 
INNER JOIN `time` ON `jogos`.`time1_id` = `time`.`id` AND `jogos`.`time2_id` = `time`.`id`

I need to know how to pull the name of time1 and of time2 through the ID.. I could do some PHP hacking, like a string replace, but I would like to pull the table times the names.

How can I do that?

Answer 1

You need to do JOIN again with the table times, example:

SELECT
t.nome as time1,
t2.nome as time2
FROM times t
INNER JOIN jogos j
ON t.id = j.time1_id
INNER JOIN times t2
ON t2.id = j.time2_id

Example: Sqlfiddle

Obs: Minimalist example to facilitate understanding.