Database, display name via ID

Asked

Viewed 64 times

3

Could someone help me.

Have the following tables

PLAYERS id name nickname status

EVENTS id date local_id

SCOREBOARDS id evento_id winner loser_id

the winning and losing fields, save the player id in the PLAYERS table, how to make a query that returns the winner’s name and the loser’s name?

Thank you

database query

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

2 answers

1

SELECT evt.data,
       venc.nome AS vencedor,
       perd.nome AS perdedor
  FROM placares p
       INNER JOIN eventos evt ON evt.id = p.evento_id
       INNER JOIN jogadores venc ON venc.id = p.vencedor_id
       INNER JOIN jogadores perd ON perd.id = p.perdedor_id

We use the clause JOIN to link two tables. In the above case we use the column vencedor_id to link to the table jogadores with the table placares and get the name of the winner (venc.nome). We do it the same way to find the loser (perdedor_id).

1

If for some reason some player record is missing from the Scoreboard table, a LEFT JOIN can display all the names, follow an example:

SELECT 
    vencedor.nome AS vencedor, perdedor.nome AS perdedor
FROM
    placares pl
        LEFT JOIN
    jogadores vencedor ON vencedor.id = pl.vencedor_id
        LEFT JOIN
    jogadores perdedor ON perdedor.id = pl.perdedor_id;

If not necessary, an INNER JOIN will suffice.

Browser other questions tagged database query

You are not signed in. Login or sign up in order to post.