Relate two mysql tables with data from the same column?

Navigation
5

I'm creating a 2014 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 did not find a search solution ... I want to make a Select that brings me the name of time1 and time2 in a query, replacing 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 time2 through ID .. I would be able to do some gambiarra for PHP, like a string replace , but I would like to pull the tables times the names.

How can I do this?

    
asked by anonymous 10.06.2014 / 22:10

1 answer

4

You need to JOIN again with the times table, for 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

Note: Minimal example for easy understanding.

    
10.06.2014 / 22:26
Oracle - How to force a field size on a function return In a service-oriented architecture, is the database decentralized?