Share content of two related tables MySql

Question

Share content of two related tables MySql

Navigation

#1 by (1 votes)

0

MYSQL

I have two tables in mysql, then I show the model: In which I need to recover all the players to which they belong to a club in specific. for example: Real Madrid and its entire staff.

QUERY

SELECT  
eqpo.nombre_equipo,
jdrs.nombre_jugador
FROM equipos eqpo
INNER JOIN ciudad cdad
ON cdad.id_ciudad = eqpo.id_ciudad_equipo
INNER JOIN jugadores jdrs
ON jdrs.id_equipo_jugador = eqpo.id_equipos

WHERE eqpo.id_equipos = '3'

REQUIREMENTS

I need to show in a first HTML table the list of football teams of the Spanish league.

By clicking on details, the list of the selected team's template is shown.

EXAMPLE 1

EXAMPLE 2

PROBLEM

but with the query I get all the names and in which the club to which it belongs is repeated, so when it is shown in the table it will be repeated 20 or 25 times the name of the Real Madrid team, of Barcelona, of Villarreal, etc.

mysql ionic angular4

asked by jecorrales 09.03.2018 в 21:07

source

1 answer

Query about catching exceptions within a function - C # clean a select in jquery

score 1 · Answer 1

What you could do is group your queries in the following way:

SELECT
    eqpo.nombre_equipo,
    GROUP_CONCAT(jdrs.nombre_jugador) AS jugadores
    FROM equipos eqpo
    INNER JOIN ciudad cdad
    ON cdad.id_ciudad = eqpo.id_ciudad_equipo
    INNER JOIN jugadores jdrs
    ON jdrs.id_equipo_jugador = eqpo.id_equipos
    WHERE eqpo.id_equipos = '3'
    GROUP BY eqpo.nombre_equipo

With this, what you are doing is asking that the results be grouped by the name of the team and that the names of the players are grouped in a single column for what they are separated by commas, an example of the result would be:

It's what I can think of, although for me it would be much more optimal to first request all available equipment for a city and then, depending on which team the user clicks on, make a second query to obtain the players otherwise you are requesting all the players for all the available equipment for a city, which translates into many possible data that the user does not really need, but that is just a comment on what you are looking for ...