Someone could help me.
Have the following tables
PLAYERS id name nickname status
EVENTS id date local_id
PLACES id event_id winner_id loser_id
the winner_id and loser_id fields, store 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
If for some reason a player record is missing in the Placards table, a LEFT JOIN can display all the names, an example follows:
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 it is not necessary, an INNER JOIN will suffice.
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 JOIN clause to link two tables. In the case above we use the vencedor_id column to bind the jogadores table to the placares table and get the name of the winner ( venc.nome ). We do the same thing to find the loser ( perdedor_id ).