¿Cómo asociar los datos de dos tablas?

[loki_gz]

Hola a todos,

Llevo varios días dándole vueltas a todo esto y por más que he buscado no encuentro nada que me de la pista de como hacer correctamente la consulta.

Os cuento: estoy creando para mi web un calendario de liga para 16 equipos. Para evitar tener que escribir jornada a jornada el nombre entero de los equipos (lo que provocaría una cantidad enorme de datos repetidos que redundaría en el peso de dicha tabla) me he creado otra tabla con los nombres de los 16 equipos, con lo que ahora tengo estas dos tablas:

CALENDARIO
Jornada (INT) (primary key)
Local1 (INT)
Visit1 (INT)
Res1 (INT)
Local2 (INT)
Visit2 (INT)
Res2 (INT)
...

EQUIPOS
ID (INT) (primary key)
Nombre (VARCHAR 30)

Como podéis apreciar en la tabla CALENDARIO, en los campos Local1, Visit1, Local2, Visit2, etc. iría un número que se correspondería con la ID del respectivo equipo en la tabla EQUIPOS.

El problema está en que no sé cómo hacer la consulta para que me muestre cada jornada de liga con el correspondiente nombre de los equipos en lugar de su número ID.

Espero vuestros consejos y agradezco de antemano vuestra ayuda.

[huesos52]

Busca información acerca de foreign keys y operador inner join.
En el foro hay múltiples ejemplos.

saludos

[loki_gz]

Gracias, algo he leído acerca de foreign keys, pero no me aclaro. Mi nivel de mysql no da para tanto.

Intentaré estudiar más sobre este tema, a ver si encuentro lo que necesito.

[loki_gz]

Sigo sin conseguir lo que busco.

La verdad es que he aprendido bastantes cosas leyendo de aquí y de allá, pero el problema que planteo sigo sin saber solucionarlo.

La complicación está en que de la tabla CALENDARIO tengo que referenciar varios campos con el ID de la tabla EQUIPOS, es decir, los campos Local1, Visit1, Local2, Visit2, etc. tienen todos una clave ID que se refiere a la PK de la tabla EQUIPOS. No sé si me he explicado bien.

En fin, a ver en qué podéis ayudarme porque ahora mismo estoy bloqueado en eso y no soy capaz de avanzar.

[jurena]

Te digo como yo lo haría:
JORNADAS
idjornada (INT) (primary key, auto_increment)
Jornada (VARCHAR)
fechainicio (DATE)
fechafin (DATE)
LIGA (INT) (ej. 2008), aunque si quieres sacar mejores estadísticas de varias ligas, debes tener una tabla LIGAS con su idliga, y liga, y aquí un foreign key referido a ese idliga de la tabla ligas.

PARTIDOS
idpartido (INT), (PRIMARY KEY, auto_increment)
refidjornada (INT) foreign key respecto al idjornada de jornadas.
nºpartido (INT) según el orden en el sorteo y presentación en la quiniela.
fecha (DATE)
horainicio (time)
campo (INT) foreign key respecto al idcampo de campos
expectadores (INT)

EQUIPOS
idequipo (INT) (primary key, auto_increment)
Nombre (VARCHAR 30)

CAMPOS
idcampo (INT) (primary key, auto_increment)
refidequipo (INT) el del equipo titular del campo
Nombre (VARCHAR 100)
Capacidad (INT)

RESULTADOSEQUIPOPARTIDO
idresequipopartido (INT) (primary key, auto_increment)
refidpartido (INT) foreign key respecto al idpartido de partidos.
refidequipo (INT) foreign key respecto a idequipo de la tabla equipos
local/visitante (INT) (0 local; 1 visitante)
golesmarcados (INT)
puntosobtenidos (INT)

Y para cada equipo que juegue un partido tendrás que rellenar un registro en la tabla resultadosequipopartido; uno será el local y otro el visitante, claro. Para sacar la quiniela tendrás que programar un poco, pero sólo un poco. En realidad, algún otro campo también podría calcularse, como es el caso de los puntos obtenidos, pero en este caso te recomiendo almacenarlo. Por lo demás, esta estructura u otra parecida te permitirá hacer estadísticas de todo tipo, goles marcados fuera de casa, número de partidos ganados en casa porcentualmente, hasta el porcentaje de asistencia respecto a las capacidades, etc. Sólo es para que te hagas una idea.

Un detalle más, si sólo vas a hacer la primera división, esto, ampliado, bastaría, pero si vas a añadir más divisiones, debes tener unas tablas generales de equipos y estadios, pero luego usar tablas intermedias de adscripción a una división un año o temporada... Y todas las tablas tendría un refid de división, y existiría una tabla divisiones, aunque eso habría que estudiarlo más despacio, pues el calendario podría no ser el mismo.

[Vun]

loki, definitivamente no hagas esa tabla JORNADAS tal y como dices, es un desperdicio de las posibilidades de las bases de datos!

Tal y como te ha expueso jurena esta perfecto, merece la pena que tomes en cuenta el post que hizo y armes la base de datos asi, me subscribo a este post y te ayudo lo que pueda :)

[loki_gz]

La solución que me propone jurena me parece muy intesante, a la vez que compleja. No sé si seré capaz de crear todo ese entramado de tablas y enlazarlas entre sí, pero... para eso estamos aquí, ¿verdad? para aprender y plantearnos nuevos retos, así que voy a intentar generar todo lo propuesto. Otra cosa será que sepa sacarle todo el partido que se le puede sacar a este sistema de tablas.

Eso si, estoy seguro de que en algún momento volveré a necesitar vuestra ayuda por lo que os pido paciencia para seguir abriéndome los ojos en este complejo mundo de las bases de datos.

Una vez más, muchas gracias por las molestias.

[Vun]

Bueno yo te voy a diseñar otras tablas por si lo quieres hacer mas simplificado, todas las tablas tienen un campo CLAVE id, de tipo auto_increment, ahi nos da igual que valor vayan tomando, es solo para identificar y unir tablas. Te pongo un esquema tal y como me lo enseñaron a mi:



Esas lineas que en algunas direcciones se dividen en varias significa que para un mismo registro de la tabla origen, van a existir VARIOS registros en la tabla destino. Por ejemplo..... una temporada concreta..... tiene varias jornadas... de ahi que las lineas se extiendan hacia jornadas. Ahora nos preguntamos ¿Una jornada concreta pertenece a varias temporadas? NO, por eso la linea de Jornadas a Temporadas es simple.

De la misma forma, un mismo equipo va a estar en varios partidos, y una jornada tiene varios partidos tambien.

¿Que significan todas estas flechas entonces? Pues significa que alli donde lleguen las flechas bifurcadas debe "heredar" el campo CLAVE de la tabla origen. Por ejemplo la tabla JORNADAS, debe tener un campo idtemporada que coincida con el campo id de temporada. Es decir JORNADAS.idtemporada=TEMPORADAS.id

Aqui estan los campos:


EQUIPOS
id
nombre

PARTIDOS
id
idjornada
idequipo1
idequipo2
golesequipo1
golesequipo2
puntosequipo1
puntosequipo2

JORNADAS
id
idtemporada
numjornada

TEMPORADAS
id
nombre



Bueno ya no escribo mas a ver si no lo vas a tomar en cuenta todo esto, espero que te haya ayudado algo mi explicacion chapucera jeje

[loki_gz]

Entiendo tu explicación, vun, pero hay un problema que es con el que me encuentro con mi planteamiento inicial y es cómo hago para vincular los campos partidos.idequipo1 y partidos.idequipo2 con la tabla equipos. Sé como vincular un campo de una tabla con otro de otra tabla, pero lo que no sé hacer es vincular 2 o más campos de una tabla con un único campo de otra tabla. No sé si me entiendes.

[Vun]

Prueba con esto, yo lo uso en varias partes de mi web y va bien

select partidos.*,equipo1.*,equipo2.* from partidos left join equipos as equipo1 on partidos.idequipo1=equipo1.id left join equipos as equipo2 on partidos.idequipo2=equipo2.id

[gnzsoloyo]

pero lo que no sé hacer es vincular 2 o más campos de una tabla con un único campo de otra tabla.

Estás vinculándolos con un campo, pero no con el mismo registro.
En la tabla PARTIDOS, ambos campos son FK de la tabla EQUIPO, de allí que la consulta de Vun sea la correcta... Lo único que haces es instanciar dos veces la tabla EQUIPO en la consulta, pero asignándole ALIAS a cada una, para que se comporten como tablas distintas en memoria.

[loki_gz]

Hola de nuevo,

Siento haberos abandonado durante dos días pero estoy enfrascado en la creación de las nuevas tablas y sobre todo en la creación de plantillas para insertar los datos en la BD y no he tenido ni un minuto para agradecer vuestras aportaciones.

Finalmente me he decidido por la propuesta de jurena (la de vun no está descartada todavía, pero es que me gustan los retos difíciles).

Mis conocimientos sobre programación son mínimos (soy un autodidacta) y la verdad es que empiezo ahora a conocer algo de lo que es mysql y php, por lo que todo este planteamiento al principio sonaba a odisea. De momento he creado todo el entramado de tablas, con sus respectivas vinculaciones (con alguna ligera modificación con respecto al original de jurena) y me estoy apoyando en Dreamweaver para crear todas las plantillas de inserción de datos que voy a necesitar (ya que hacerlo directamente en la BD es del todo absurdo, como ya sabéis). Debido a mis escasos conocimientos tropiezo una vez tras otra y me lleva una enormidad avanzar en cada paso, pero de momento avanzo.

No digo que no os vaya a necesitar más adelante (aunque probablemente sean dudas sobre php más que sobre mysql), pero de momento ya veis que me voy arreglando.

De nuevo agradezco vuestra ayuda enormemente.

Un saludo

[Vun]

Yo tengo tu post en mis subscripciones, asi que cada pocos dias le echo un ojo, suerte y a echarle horas!

[loki_gz]

Hola de nuevo,

Creo que voy a necesitar ayuda de nuevo. Se trata de las plantillas que comenté que estaba creando para la inserción de datos.

El caso es que cuando añado los partidos de cada jornada quiero que me vaya mostrando los ya añadidos pertenecientes a esa misma jornada. Se me ha ocurrido que la forma de filtrar esos resultados sea poniendo en la consulta un WHERE donde PARTIDOS.RefIdJornada sea igual a la última IdJornada creada. El problema es que no sé como expresar eso en un WHERE.

También es posible que exista una solución más fácil que la que yo propongo por lo que estoy abierto a otras posibilidades.

Agradezco vuestra ayuda de antemano

[Vun]

Recuerda que se incluyeron las sub-consultas en Mysql, osea primero haces una consulta para obtener la ultima jornada:

Select max(ID) from jornadas

Y luego lo añadimos a tu consulta y quedaria ...... WHERE (Select max(ID) from jornadas)

[loki_gz]

Gracias Vun,

No sabía que había que expresar así este filtro con WHERE.

Ya lo he solucionado, seguimos adelante...

[loki_gz]

Bueno, pues vuelvo a necesitar vuestra ayuda.

¿Cómo puedo hacer una consulta para que me muestre un partido con su resultado y los dos rivales en un sólo registro, tal y como está planteada la estructura de tablas de jurena? Como podéis ver, para almacenar a cada rival de un partido y los goles de este en la tabla resultados se utilizan dos registros pero de esta manera me resulta muy complicado mostrar cada jornada del campeonato y sobre todo si utilizo la repetición de registros para motrar los resultados completos de un SELECT.

Le he dado muchas vueltas pero las soluciones que se me ocurren son muy complicadas y de dudosa efectividad. A lo mejor existe una solución sencillísima que a mi no se me ocurre o a lo mejor la solución es tan complicada como la que tengo en mente. Es por eso que necesito otra vez vuestra ayuda, a ver si me guiáis hacia la luz nuevamente.

[jurena]

A ver, loki_gz,
mira esta consultas y los resultados que obtienes de ellas (sólo te muestro algunos datos introducidos por mí y referidos a dos partidos de la primera jornada en los que están implicados los equipos, equipo1, equipo2, equipo3, equipo4:

Código sql:

SELECT j.jornada, p.numeropartido, e.nombre, rep.localvisitante, rep.golesmarcados, rep.puntosobtenidos
FROM `resultadosequipopartido` rep
INNER JOIN partidos p ON rep.refidpartido = p.idpartido
 INNER JOIN jornadas j ON j.idjornada = p.refidjornada
 INNER JOIN equipos e ON e.idequipo = rep.refidequipo
 WHERE j.idjornada = 1
 ORDER BY p.numeropartido
 

jornada
numeropartido
nombre
localvisitante
golesmarcados
puntosobtenidos

Primera Jornada 1 equipo1 0 2 3
Primera Jornada 1 equipo2 1 1 0
Primera Jornada 2 equipo3 1 1 1
Primera Jornada 2 equipo4 0 1 1

Estos datos tienen que ser luego trabajados con algo de programación para presentarlos como quieres.

Esta segunda opción, algo más lenta, te ofrecerá los resultados más o menos como quieres:

Código sql:

SELECT j.jornada,p.numeropartido, GROUP_CONCAT(e.nombre ORDER BY rep.localvisitante separator '-') equipos, 
 GROUP_CONCAT(CAST(rep.golesmarcados AS CHAR) ORDER BY rep.localvisitante separator '-') resultado,
 GROUP_CONCAT(CAST(rep.puntosobtenidos AS CHAR) ORDER BY rep.localvisitante separator '-') puntos
FROM `resultadosequipopartido` rep INNER JOIN partidos p ON rep.refidpartido = p.idpartido
 INNER JOIN jornadas j ON j.idjornada = p.refidjornada
 INNER JOIN equipos e ON e.idequipo = rep.refidequipo WHERE j.idjornada = 1
 GROUP BY rep.refidpartido ORDER BY p.numeropartido
 

jornada
numeropartido
equipos
resultado
puntos

Primera Jornada 1 equipo1-equipo2 2-1 3-0
Primera Jornada 2 equipo4-equipo3 1-1 1-1

[loki_gz]

Gracias Jurena, la verdad es que desconocía esa función y me ha venido fantástica. Era justo lo que necesitaba.

Sigo adelante con la programación del sitio.

[loki_gz]

Hola nuevamente,

Lamento tener que recurrir otra vez a vuestos conocimientos pero me he encontrado con un nuevo obstáculo.

Estoy creando la tabla clasificatoria a partir de las tablas que ya todos conocemos. A partir de la tabla RESULTADOSEQUIPOPARTIDO creo una consulta que me proporciona todos los datos que necesito:

PJ (Partidos Jugados)
GF (Goles a Favor)
GC (Goles en Contra)
PT (Puntos)

Hasta aquí, sin problema, lo he conseguido.

Pero... hay tres campos con los que no puedo (P. Ganados, P. Empatados, P. Perdidos). ¿Cómo puedo hacer para contar estos valores a partir del campo "puntosobtenidos"? La teoría es fácil: Si "puntosobtenidos"=3 entonces P.Ganados+1, si "puntosobtenidos"=1 entonces P.Empatados+1, si "puntosobtenidos"=0 entonces P.Perdidos+1. Sé como hacer una consulta donde me devuelva uno de estos valores, sería así:

Código:

SELECT refidequipo, COUNT(*) FROM RESULTADOSEQUIPOPARTIDO
WHERE puntosobtenidos=3 GROUP BY refidequipo

El problema está en cómo creo las tres consultas y todo dentro de la consulta principal de la que os he hablado al principio.

Espero que me hayáis entendido y nuevamente agradezco vuestra ayuda y molestias.

[jurena]

También puedes hacerlo de otras maneras, pero esta es una de ellas:

Código sql:

SELECT e.nombre, SUM(IF(puntosobtenidos = 3,1,0)) ganados,
 SUM(IF(puntosobtenidos = 1,1,0)) empatados,
 SUM(IF(puntosobtenidos = 0,1,0)) perdidos
 FROM `resultadosequipopartido` rep
 INNER JOIN equipos e ON rep.refidequipo = e.idequipo GROUP BY e.idequipo
 

[loki_gz]

Que simple! Y yo liándome con subconsultas y cadenas interminables.

Gracias por enésima vez, Jurena