Select with two fields as FKs of the same table

Question

Select with two fields as FKs of the same table

Navigation

#1 by (1 votes)

1

I would like to know how to mount a select in Oracle to exit the names of teams that participated in a game, using the structure below:

Hereisthecodeforcreatingthetable: Code for creating the table

I think the output would be something like:

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

sql oracle

asked by anonymous 28.11.2018 / 02:34

1 answer

Delete all records from a database except a user How do I make a button draw something new on a screen with tkinter?

score 1 · Accepted Answer

You can make two joins for the same team table, using different aliases , linking a join to the team's identifying field 1 and the other to the team identification field 2, in the game table:

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 in SQL Fiddle .