Select with two fields like Fks from the same table

Question

I’d like to know how to ride a select in Oracle to perform the departure of the names of the teams that participated in a game, using the structure below:

Follow the code to create the table: Code for table creation

I think the way out would be something like:

Código do Jogo | Nome do Time 1 | Nome do Time 2 | Data do Jogo

Answer 1

You can do two joins to the same table of teams, using aliases different, linking a Join to the identification field of team 1 and the other to the identification field of team 2, in the table of games:

select
  j.A_CODIGO_JOGO_PK AS "Código do Jogo",
  j.A_DATA_JOGO AS "Data do Jogo",
  t1.A_NOME_TIME AS "Nome do Time 1",
  t2.A_NOME_TIME AS "Nome do Time 2"
from T_JOGO j
inner join T_TIME t1 on t1.A_CODIGO_TIME_PK = j.A_CODIGO_TIME1_FK
inner join T_TIME t2 on t2.A_CODIGO_TIME_PK = j.A_CODIGO_TIME2_FK

See the code working on SQL Fiddle.