Duplicate data - Double inner join

Question

Duplicate data - Double inner join

Navigation

#1 by (4 votes)

3

I have a question about a MYSQL query.

I have the following tables:

tabla: partidos
id_partido,
id_local_partido,
id_visita_partido,
estado_partido,
rdolocal_partido,
rdovisita_partido,
cancha_partido,
hora_partido,
torneo_partido,
jornada_partido,
fecha_partido

tabla equipos
id_equipo,
nombre_equipo

The issue is, I have to make a query that throws me the description of the game, but I can not make me put, instead of the team id, the name ... I've tried several days but I'm stuck.

The best I could do was make 2 inner join but repeat the records.

SELECT partidos.* , equipos.* FROM partidos INNER JOIN equipos ON ( 
equipos.id_equipo LIKE partidos.id_local_partido ) OR (equipos.id_equipo 
LIKE partidos.id_visita_partido) WHERE partidos.jornada_partido = '1'

The result of this consultation is 18 records, but I need them to be 9 and show the name of the LOCAL team and the VISITOR since there are 9 matches per day.

I need to show:

id_partido | LOCAL | rdolocal_partido | VISITANTE | rdovisita_partido | cancha | hora

1   |  RIVER   |    0     |   BOCA    |    0    | CANCHA 1  |  13:00hs

I hope you explained, thank you very much!

mysql

asked by Daniel 27.06.2018 в 04:19

source

1 answer

C # Threads error the process can not access the file because that being used in another process Extract part of a chain between two specific points

score 4 · Answer 1

Your problem is this:

INNER JOIN equipos 
    ON ( equipos.id_equipo LIKE partidos.id_local_partido ) 
         OR 
      (equipos.id_equipo LIKE partidos.id_visita_partido)

You are doing JOIN for each partido but you match two rows of equipos so naturally you are duplicating the rows.

This should be what you are looking for:

SELECT  p.id_partido, 
        l.nombre_equipo,
        p.rdolocal_partido,
        v.nombre_equipo,
        p.rdovisita_partido,
        p.cancha_partido, 
        p.hora_partido
        FROM partidos p
        INNER JOIN equipos l
              ON ( l.id_equipo = p.id_local_partido)
        INNER JOIN equipos v
              ON ( v.id_equipo = p.id_visita_partido)
        WHERE p.jornada_partido = '1'

As you can see we make two INNER JOIN to obtain the data of the local team and the visitor. He also notes the use of table aliases that make writing much easier. And finally it does not seem that the LIKE is the appropriate operator to match the ID of the equipment, I imagine that the = is appropriate.