[SOLUCIONADO] Inner Join une un campo de una tabla a varios de otra tabla

[temerariomalaga]

Buenas, estoy intentado unir una clave primaria con las claves foraneas a las que referencia.
Quiero unir el Codigo_arbitro de la tabla equipo_arbitral al Anotador, Crono y Op24 de la tabla partido.
Para unir uno solo no hay problemas pero si intento unir al crono despues de haber unido el anotador me devuelve el error not unique table alias p.
Este es el código:

Código MySQL:

SELECT Codigo_arbitro, Hora, PABELLON.Codigo_localidad AS 'Loca' FROM EQUIPO_ARBITRAL
        INNER JOIN PARTIDO P ON EQUIPO_ARBITRAL.Codigo_arbitro=P.Anotador
        INNER JOIN PABELLON ON P.Codigo_pabellon=PABELLON.Codigo_pabellon
 

[quimfv]

Pues cambia los alias de las tablas ....no?

Tabla ---> alias
EQUIPO_ARBITRAL ---> ea
PARTIDO ---> p
PABELLON ---> l <---(ele de local)

Código MySQL:

SELECT ea.Codigo_arbitro, 
             p.Hora, 
             l.Codigo_localidad AS 'Local' 
FROM EQUIPO_ARBITRAL ea
               INNER JOIN PARTIDO p ON ea.Codigo_arbitro=p.Anotador
               INNER JOIN PABELLON l ON p.Codigo_pabellon=l.Codigo_pabellon;
 

Fijate que a cada campo que sale en el SELECT le pongo el alias de su tabla, repasa si esta bien.

Asi deberia funcionar, pero claro nohe visto la estructura de tus tablas.....

Me explicas que tienen que ver el codigo arbitral con el anotador

Código MySQL:

ON ea.Codigo_arbitro=p.Anotador
 

[temerariomalaga]

Todos los miembros del equipo arbitral(arbitro principal, arbitro auxiliar, anotador, crono y operador) se encuentran en la tabla equipo arbitral. En un partido un arbitro puede ser principal pero en otro puede ser auxiliar por ejemplo, por eso todos los he puesto en una misma tabla. Ya he puesto alias a todos los campos pero al ejecutar la consulta no devuelve ningún valor. Aqui está la estructura de las dos tablas.

Código MySQL:

CREATE TABLE IF NOT EXISTS `equipo_arbitral` (
  `Codigo_arbitro` int(11) NOT NULL AUTO_INCREMENT,
  `Licencia` varchar(4) DEFAULT NULL,
  `Nombre` varchar(20) DEFAULT NULL,
  `Apellidos` varchar(20) DEFAULT NULL,
  `Email` varchar(20) DEFAULT NULL,
  `Telefono1` int(9) DEFAULT NULL,
  `Telefono2` int(9) DEFAULT NULL,
  `Cuenta` int(20) DEFAULT NULL,
  `Cod_localidad` int(3) DEFAULT NULL,
  PRIMARY KEY (`Codigo_arbitro`));
 
CREATE TABLE IF NOT EXISTS `partido` (
  `Codigo_Partido` int(11) NOT NULL AUTO_INCREMENT,
  `EquipoA` int(11) NOT NULL,
  `EquipoB` int(11) NOT NULL,
  `Fecha` date DEFAULT NULL,
  `Hora` time DEFAULT NULL,
  `Codigo_pabellon` int(11) NOT NULL,
  `Codigo_categoria` int(11) NOT NULL,
  `Tipo` varchar(10) DEFAULT NULL,
  `Jornada` int(11) NOT NULL,
  `ArbPrin` int(11) DEFAULT NULL,
  `ArbAux` int(11) DEFAULT NULL,
  `Anotador` int(11) DEFAULT NULL,
  `Crono` int(11) DEFAULT NULL,
  `Op24` int(11) DEFAULT NULL,
  `Coche` int(11) DEFAULT NULL,
  PRIMARY KEY (`Codigo_Partido`),
  KEY `EquipoA` (`EquipoA`),
  KEY `EquipoB` (`EquipoB`),
  KEY `Codigo_categoria` (`Codigo_categoria`),
  KEY `ArbPrin` (`ArbPrin`),
  KEY `ArbAux` (`ArbAux`),
  KEY `Anotador` (`Anotador`),
  KEY `Crono` (`Crono`),
  KEY `Op24` (`Op24`),
  KEY `Codigo_pabellon` (`Codigo_pabellon`),
  KEY `Coche` (`Coche`));
 

PD: El codigo de la estructura lo he copiado del archivo de phpmyadmin. Al crearla lo hice de otra forma, ArbPrin, ArbAux, etc van referencian al codigo_arbitro de la tabla equipo_arbitral

[quimfv]

Código MySQL:

CREATE TABLE IF NOT EXISTS `equipo_arbitral` (
  `Codigo_arbitro` int(11) NOT NULL AUTO_INCREMENT,
  `Licencia` varchar(4) DEFAULT NULL,
  `Nombre` varchar(20) DEFAULT NULL,
  `Apellidos` varchar(20) DEFAULT NULL,
  `Email` varchar(20) DEFAULT NULL,
  `Telefono1` int(9) DEFAULT NULL,
  `Telefono2` int(9) DEFAULT NULL,
  `Cuenta` int(20) DEFAULT NULL,
  `Cod_localidad` int(3) DEFAULT NULL,
  PRIMARY KEY (`Codigo_arbitro`));
 

Luego en la tabla "equipo_arbitral" tienes los arbitros (individuos) no los equipos arbitrales, entendiendo equipo arbitral como el grupo de arbitros que pintan un partido. Seri mucho mas claro llamar a esa tabla "Arbitros" puesto que es lo que la tabla de arbitros.... las cosas pore su nombre.

Estos, los equipos arbitrales estan el la tabla partido: arbitro principal, arbitro auxiliar, anotador, crono y operador, entendiendo equipo arbitral como el grupo de arbitros que pintan un partido.

`ArbPrin` int(11) DEFAULT NULL,
`ArbAux` int(11) DEFAULT NULL,
`Anotador` int(11) DEFAULT NULL,
`Crono` int(11) DEFAULT NULL,
`Op24` int(11) DEFAULT NULL,


todos esos campos deberían ser FK a la tabla "equipo_arbitral" ya que todos esos campos deben conetener un elemento existente en la tabla "equipo_arbitral"

Para conseguir lo que buscas debes hacer join de las tablas tantas veces como relaciones tengan.

Código MySQL:

SELECT APrin.`Codigo_arbitro` CodigoAPrin, APrin.`Nombre` NombreAPrin,APrin.`Apellidos` ApellidosAPrin,
         AAux.`Codigo_arbitro` CodigoAAux, AAux.`Nombre` NombreAAux,AAux.`Apellidos` ApellidosAAux,
         AAnot.`Codigo_arbitro` CodigoAAnot, AAnot.`Nombre` NombreAAnot,AAnot.`Apellidos` ApellidosAAnot,
         ACrono.`Codigo_arbitro` CodigoACrono, ACrono.`Nombre` NombreACrono,ACrono.`Apellidos` ApellidosACrono,
         AOp24.`Codigo_arbitro` CodigoAOp24, AOp24.`Nombre` NombreAOp24,ACOp24.`Apellidos` ApellidosAOp24,
         p.Hora,
         tPab.Codigo_localidad AS 'Local' 
  FROM `partido` p INNER JOIN `equipo_arbitral` APrin ON p.`ArbPrin`=APrin.`Codigo_arbitro`
                   INNER JOIN `equipo_arbitral` AAux ON p.`ArbAux`=AAux.`Codigo_arbitro` 
                   INNER JOIN `equipo_arbitral` AAnot ON p.`Anotador`=AAnot.`Codigo_arbitro` 
                   INNER JOIN `equipo_arbitral` ACrono ON p.`Crono`=ACrono.`Codigo_arbitro` 
                   INNER JOIN `equipo_arbitral` AOp24 ON p.`Op24`=AOp24.`Codigo_arbitro` 
                   INNER JOIN `pabellon` tPab ON P.Codigo_pabellon=tPab.Codigo_pabellon;
 

Para obtener los datos del Arbitro principal de un partido debes relacionar el partido con la tabla donde tengas los arbitros por el campo de Arbitro principal, para los del auxiliar debes relacionar la misma tabla paro esta vez por el campo de arbitro auxiliar y así sucesivamente.....

Se entiende?

[temerariomalaga]

Ya tenia las FK en la creacion de la tabla partidos pero como copie la creacion del phpmyadmin se me olvido ponerle las lineas que añaden las claves foraneas.
Después, la tabla se llama equipo arbitral porque es una tabla con una jerarquia con dos tablas, arbitro y oficial, ya que un arbitro puede ser oficial tambien pero no todos lo son, al igual que pasa con los oficiales que no suelen ser arbitros normalmente. Depués el tema de la consulta que no me funciona. Esta consulta está dentro de una macroconsulta que tengo (la que publiqué hace un par de dias) que controla si un arbitro esta disponible, si tiene un partido asignado y la hora de ese partido para no designarle un partido a la misma hora.
Lo que yo quiero que me devuelva esta consulta en concreto es el codigo de los arbitros que tienen un partido y la hora del partido para hacer la criba que expliqué arriba y para ello tengo que comprobar si el arbitro en cuestion tiene algun partido ya designado para que a la hora de hacer designaciones solo me aparezcan los arbitros que puedan acudir al partido. No se si me he explicado bien.

[temerariomalaga]

He modificado un poco la consulta y si hago left join aparecen todos los codigos con las horas a null excepto los que tienen hora asignada. ¿Habría otra forma para que solo devuelva las que tienen algún dato o sin repetir 3 veces la hora?

Código MySQL:

SELECT Codigo_arbitro, P1.Hora, P2.Hora, P3.Hora FROM EQUIPO_ARBITRAL
        LEFT JOIN PARTIDO P1 ON EQUIPO_ARBITRAL.Codigo_arbitro=P1.Anotador
        LEFT JOIN PARTIDO P2 ON EQUIPO_ARBITRAL.Codigo_arbitro=P2.Crono
        LEFT JOIN PARTIDO P3 ON EQUIPO_ARBITRAL.Codigo_arbitro=P3.Op24;
 

[quimfv]

Yo haria lo siguiente

Código MySQL:

SELECT EA.Codigo_arbitro FROM EQUIPO_ARBITRAL EA
        INNER JOIN PARTIDO P ON EA.Codigo_arbitro=P.Anotador
WHERE P.Hora='YYYY-mm-dd HH:MM:SS'
UNION ALL
SELECT EA.Codigo_arbitro FROM EQUIPO_ARBITRAL EA
        INNER JOIN PARTIDO P ON EA.Codigo_arbitro=P.Crono
WHERE P.Hora='YYYY-mm-dd HH:MM:SS'
UNION ALL
SELECT EA.Codigo_arbitro FROM EQUIPO_ARBITRAL EA
        INNER JOIN PARTIDO P ON EA.Codigo_arbitro=P.Op24
WHERE P.Hora='YYYY-mm-dd HH:MM:SS';
 

Esto te da la lista de arbitros que tienen asignado un partido a una hora determinada (hora datetime). Con esto es facil encontrar los que NO tinen partido y por lo tanto estan disponibles.

Quizas deberías trabajar con un margen de tiempo para permitir los desplazamientos de los arbitros, no sea que pongas dos partidos consecutivos en el tiempo a un mismo arbitro en lugares muy alejados....

[temerariomalaga]

Iniciado por quimfv

Yo haria lo siguiente
Quizas deberías trabajar con un margen de tiempo para permitir los desplazamientos de los arbitros, no sea que pongas dos partidos consecutivos en el tiempo a un mismo arbitro en lugares muy alejados....

Eso lo iba a controlar con una tabla de desplazamientos que tiene los km entre distintas localidades, así si es la misma localidad hay un margen de 1 hora 45 y si está en un rango de 5km pues de 2 horas, etc. Ya probaré mañana tu solución y ya digo si funciona o tengo algún problema. Gracias.

[temerariomalaga]

Ahora si funciona perfectamente, la única pega es que me saca los resultados repetidos muchas veces pero eso ya es cosa de PHP. Muchas gracias por tu ayuda quim