Home / Questions / 446065
Accepted
Pablo Grassi
Asked: 2022-04-22 11:14:00 +0800 CST 2022-04-22 11:14:00 +0800 CST

mysql query with two tables

  • 772

I have a table of players where I have all the data of the players, in this case the name and surname, then I have another table where I have match results, in that match results table I have two fields called player_a and player_b ( these two fields are part of the same record), these fields have the id that corresponds to the id of the players table. What I need is to know how I get the name and surname of the two players in the same query. I can only get the first and last name of one of the players but not both. I leave the query I made as an example.

SELECT nombre.jugadores, apellido.jugadores from jugadores INNER JOIN juegos ON jugadores.id = juegos.jugador_a WHERE id = 170
mysql

1 Answers

  1. Best Answer

    You would have to include the table twice jugadoresin the query, once to relate it to the column jugador_aand once to relate it to the jugador_b. Then the names would be obtained for each respective table (I have given the aliases a and btables for this case).

    The only doubt here is the iddel WHERE, I've assumed it refers to the column idof the table juegos:

    SELECT 
    a.nombre AS a_nombre,
    a.apellido AS a_apellido,
    b.nombre AS b_nombre,
    b.apellido AS b_apellido
FROM juegos AS j
    INNER JOIN jugadores AS a ON j.jugador_a = a.id
    INNER JOIN jugadores AS b ON j.jugador_b = b.id
WHERE j.id = 170
    • 0