Consultas: inner, left o right join?

[Subotai]

Buenas gente, tengo una base de datos futbolera con unas cuantas tablas, entre ellas estas dos, de las que tengo que hacer una consulta que no me sale
Os pongo solo los campos que necesito para la consulta para poder simplificar :P

Tabla Partidos:
-------------------------
ID_PARTIDO
ID_JORNADA
ID_LOCAL
ID_VISITANTE
HORA

/*********************************/

Tabla Equipos:
-------------------------
ID_EQUIPO
NOMBRE

/********************************

Bien, así suficiente. La consulta tiene que sacar algo tan sencillo ¬¬ como lo siguiente:

A partir de un ID_JORNADA determinado obtener la información de quienes juegan y a que hora, algo así como:

España Vs Rusia a las 8:45



Hasta ahora iba por este camino:

$sql_partidos = "SELECT equipos.NOMBRE, equipos_1.NOMBRE, partidos.HORA
FROM equipos AS equipos_1, equipos INNER JOIN partidos ON (equipos.ID_EQUIPO = partidos.ID_VISITANTE) AND (equipos.ID_EQUIPO = partidos.ID_LOCAL) WHERE partidos.ID_JORNADA = '".$jornadas['ID_JORNADA']."' ORDER BY partidos.HORA";

No se si voy por el buen camino, esa sql que os he puesto no da ningún error, pero no saca ningún registro cuando debería obtener más de uno...
He comprobado que no se debe a que no haya datos para sacar o que los id's no se correlacionen, etc.. los datos están bien.

Nota aclaratoria:No hago la consulta en base al ID_PARTIDO porque la intención es obtener el listado de todos los partidos que se juegan en la jornada X

Espero que alguien pueda ayudarme :P

PD:PODEMOOOS!!

[quimfv]

$sql_partidos = "SELECT equipos.NOMBRE,
equipos_1.NOMBRE,
partidos.HORA
FROM (equipos INNER JOIN partidos
ON equipos.ID_EQUIPO = partidos.ID_LOCAL) INNER JOIN equipos AS equipos_1
ON equipos_1.ID_EQUIPO = partidos.ID_VISITANTE
WHERE partidos.ID_JORNADA = '".$jornadas['ID_JORNADA']."'
ORDER BY partidos.HORA";

Más claro

$sql_partidos = "SELECT local.NOMBRE,
visitante.NOMBRE,
partidos.HORA
FROM (equipos AS local INNER JOIN partidos
ON local.ID_EQUIPO = partidos.ID_LOCAL) INNER JOIN equipos AS visitante
ON visitante.ID_EQUIPO = partidos.ID_VISITANTE
WHERE partidos.ID_JORNADA = '".$jornadas['ID_JORNADA']."'
ORDER BY partidos.HORA";

Si o si?

Quim

[Subotai]

OH! que CRACK!

Gracias!!!!!!!!!
La consulta que me posteas funciona de maravilla en el mysql, ahora ya veré que ocurre con el php, pero esa es la parte fácil jejejejje

MIL GRACIASSSSS

[jurena]

Yo la suelo hacer así:
SELECT l.nombre, v.nombre, p.hora from (equipos l, equipos v) INNER JOIN partidos p on l.ID_EQUIPO = p.ID_LOCAL AND v.ID_EQUIPO= p.ID_VISITANTE

Y mi pregunta para nuestro Quim y para los técnicos es: ¿Se trata de lo mismo que ha hecho Quim?
¿De cuál se obtiene mejor rendimiento?

[quimfv]

Que respondan los técnicos...

No se, la mía creo que es más SQL estándar pero en definitiva acabas haciendo lo mismo relacionar partidos por los dos id con equipos (dos veces claro)...

Que los técnicos hablen.... habría que tener las tablas y hacer el explain plan de las dos consultas para ver cual va más rápida o que hacen en cada caso y valorar cual tiene mas costes... o si simplemente son equivalentes...

Yo, particularmente, tendría que repasar las funciones de auditoria de MySQL... ya sabemos que son más limitadas que en otros motores.

En cualquier caso en una base de datos en que se guarde una liga de futbol como parece que es el caso no se si és muy eficiente perder el tiempo en optimizar mucho las consultas que siempre seran sobre tablas con un numero reducido o muy reducido de registros...

Quim

[jurena]

Gracias, Quim.
Me da la impresión de que la tuya es más eficiente, pero no tengo datos para afirmarlo.
Subotai,
En cuanto a la estructura de la tabla, yo pienso que tener que volver a la tabla para cargar los nombres, y no separar los locales y visitantes en registros distintos, es una estrategia que te "saldrá cara" a la larga, sobre todo cuando quieras realizar estadísticas sobre los equipos.

edito para corregir un error de redacción en mi anterior post:

donde dice: Y mi pregunta para nuestro Quim y para los técnicos es
debe decir Y mi pregunta para nuestro Quim y para los otros técnicos es...

el afecto me hace a veces olvidar lo que es cada uno, sobre todo los amigos, Quim.

[quimfv]

jurena:El afecto no te haga pensar que lo se todo hay tecnicos mucho mas sabios que yo y en todo caso aprendemos todos de todos...

Quim

[Subotai]

Buenass, no había visto estas respuestas.. :P

Jurena, seguro que a la larga me saldría cara en otro tipo de BBDD, pero en una liga de fútbol con liga y copa son unos 72 partidos como mucho.. no me preocupa demasiado, además una vez terminada la liga se crearía un histórico para futuras consultas y se truncaría la tabla para la siguiente temporada..

Gracias por responder, ahora voy a mirar tu respuesta en el otro post :P

[jurena]

Subotai,

yo me quedaría con la consulta de Quimfv, porque creo que es la consulta con mejor rendimiento. Respecto a lo otro, a la estructura de las tablas, yo usaría la otra, es decir, no tendría una tabla de jornada o partido con dos campos uno para cada equipo, sino una tabla de jornadas con los id de jornada y atributos de jornada como la fecha, etc., luego una tabla de partidos cada uno con su id y un idjornada FK, atributos como lugar, hora, etc., y tampoco ahí incluiría los resultados de los equipos sino en una tabla de resultadopartidos, con su propio idresultados PK, idpartido FK, idequipo FK, local/visitante ( 0/1), vic/der/emp (0/1/2), puntos (0,1,3), etc. Eso me permitiría luego llevar a cabo estadísticas sin tantos problemas y también me permitiría mostrar los contendientes de un partido, aunque me obligase a programar un poco más. Porque, imagina cómo sacarías los puntos de un equipo desde la base teniendo que ir a ella en dos ocasiones para mirar ambos campos... eso para una consulta fácil, pero para una estadística más compleja, por ejemplo la comparación entre las medias de empates de un equipo alcanzada hasta una determinada fecha... Piensa en si necesitas algo así y cómo lo harías...

[Subotai]

Iniciado por jurena

Subotai,

...sino una tabla de jornadas con los id de jornada y atributos de jornada como la fecha, etc., luego una tabla de partidos cada uno con su id y un idjornada FK, atributos como lugar, hora, etc., y tampoco ahí incluiría los resultados de los equipos sino en una tabla de resultadopartidos, con su propio idresultados PK, idpartido FK, idequipo FK, local/visitante ( 0/1), vic/der/emp (0/1/2), puntos (0,1,3), etc....

Es que así estaba desde el principio, lo que pasa es que para simplificar mi "consulta" (aquí en el foro) no puse toooodos los campos y tablas, por no haceros un lío.
De echo ni siquiera guardo los resultados, sino que los calculo a la hora de visualizar y una vez cerrada la jornada se queda en una tabla fija clasificación que solo sería actualizada cada fin de jornada.

[jurena]

Si eso es así, lo que no entiendo es esto del primer post
Tabla Partidos:
-------------------------
ID_PARTIDO
ID_JORNADA
ID_LOCAL
ID_VISITANTE
HORA

cuando debería ser, según entiendo yo:
------------------
TABLA PARTIDO
------------------
ID_PARTIDO
HORA
REFIDJORNADA

-----------------
TABLA RESULTADOPARTIDO
-----------------
ID_RESULTADOPARTIDO
REFID_PARTIDO
ID_EQUIPO
LOCAL/VISITANTE
V/D/E

por no poner más campos. De todas formas, habrá quien te diga que se puede hacer de ambas maneras, pero yo prefiero esta segunda (¿cuestión de gustos? No, de estructura).

Suerte

[Subotai]

Hum... viendo tu respuesta veo que no nos hemos entendido.. mira te pongo las tablas tal cual están

(En su día tuve dudas de si crear esta tabla o incluirla directamente en partidos, pero por escalabilidad, opté por crearla)

JORNADAS
----------------------------------------------------------------
ID_JORNADA
ID_COMPETICION
FECHA


En la tabla partidos opté por poner el id de ambos equipos y marcarlos directamente como local uno y visitante el otro, de otra forma habría un registro por equipo y se repetirían datos (como la hora) y no interesa

PARTIDOS
----------------------------------------------------------------
ID_PARTIDO
ID_JORNADA
ID_LOCAL
ID_VISITANTE
HORA
DISPUTADO


TARJETAS
----------------------------------------------------------------
ID_TARJETA
ID_PARTIDO
ID_JUGADOR
TIPO
MINUTO


GOLES
----------------------------------------------------------------
ID_GOL
ID_COMPETICION
ID_PARTIDO
ID_JUGADOR
MINUTO


JUGADORES
----------------------------------------------------------------
ID_JUGADOR
ID_EQUIPO
NOMBRE
APELLIDOS
(resto de campos de datos...)


EQUIPOS
----------------------------------------------------------------
ID_EQUIPO
ID_CAPITAN
NOMBRE
(resto de campos de datos...)

Como te decía, no guardo el resultado de cada encuentro en ninguna tabla, puesto que se puede calcular.
Lo que queda es una tabla de número de registros fijos, para dejar el resultado de la clasificación al cerrar cada jornada (puntos, empatados, perdidos, ganados...)

[jurena]

No podría decirte que mi solución es mejor, y probablemente no lo sea, pero te diré que sí nos hemos entendido, al menos yo imaginé que esta era tu propuesta. No tengo más razones para argumentar en favor de la otra propuesta que la que te he dicho ya.
Suerte