INNER JOIN con tres tablas y función SUM

[ecarlevaro]

Bueno, siendo la 01:59 AM me rendí del infalibre método de prueba y error (agotado ya el método de leer el manual de MySQL) y decidí consultar.

Tengo tres tablas, ARTICULOS, IMAGENES, ESTADISTICAS.

Las tres tablas comparten la columna idarticulo.

Por cada registro de artículos puede haber cero o muchos registros en imágenes (las imágenes del artículo).

Por cada registro de articulos puede haber cero o muchos registros en estadísticas (básicamente cada visita de cada fecha a cada artículo se registra en ESTADÍSTICAS).

Yo necesito saber el idarticulo, la primera imagen guardada, y el total de visitas (para todas las fechas) por cada articulo.

Es decir, el resultado que necesito sería:

IDARTICULO IMAGEN VISITAS
1 hola.jpg 38
2 gaylord.jpg 12
Donde visitas es la suma de todas las visitas a cada artículo para todas las fechas.

En ESTADISTICAS los registros basicamente son así:

IDARTICULO FECHA VISITAS
1 18/01 1
1 19/01 2
2 20/01 5

Entonces para obtener el total de visitas por artículo hago un SUM(estadisticas.visitas) y un GROUP BY articulos.idarticulo.

Ahora bien, para conseguir la primer imagen de cada artículo, me aprovecho de la columna idimagen de la tabla IMAGENES, y lo consigo con MIN(imagenes.idimg) y imagenes.imagen, también con un GROUP BY articulos.idarticulo que me devuelve una imagen por cada artículo.

El problema es que cuando junto todo en una consulta, todo anda bien, excepto el SUM(estadisticas.visitas) que devuelve la cantidad de imágenes para cada artículo, no suma las visitas, sino devuelve la cantidad de imágenes (de registros en la tabla IMAGENES para cada artículo) en vez de sumar las visitas suma la cantidad de registros de imagen para cada artículo.

La consulta que hago es:

Código MySQL:

SELECT articulos.idarticulo,SUM(estadisticas.visitas) as visitas, imagenes.imagen,MIN(imagenes.idimg) FROM (articulos LEFT JOIN estadisticas ON articulos.idarticulo = estadisticas.idarticulo) INNER JOIN imagenes ON articulos.idarticulo = imagenes.idarticulo GROUP BY articulos.idarticulo ORDER BY idarticulo ASC
 

Uso LEFT JOIN pues no todos los artículos tienen un registro en ESTADÍSTICAS pues algunos no tienen visitas todavía.

Y me devuelve algo así:

IDARTICULO IMAGEN VISITAS
1 hola.jpg 5
2 gaylord.jpg 3

donde 5 y 3 ¡no son las sumas de las visitas para las distintas fechas del artículo 1 y 2! son la cantidad de imágenes (registros en IMAGENES) del artículo 1 y 2.

Si alguien anda con suficientes ganas de meterse en este problema le agredeceré, mientras seguiré probando...

[jurena]

Prueba con algo así:
SELECT a.idarticulo, a.articulo, ifnull(i.imagen, 'sin imagen'), IFNULL(SUM(e.visitas),0) totalvisitas FROM articulos a LEFT JOIN imagenes i ON a.idarticulo = i.idarticulo LEFT JOIN estadisticas e ON a.idarticulo = e.idarticulo GROUP BY a.idarticulo

[quimfv]

Código MySQL:

SELECT articulos.idArticulo, 
             articulos.nombre, 
             IFNULL(sbcimagenes.imagen,"noimagen.jpg") as img, 
             IFNULL(SbCVisitas.SuVisitas,0) AS Visitas
FROM (articulos 
               LEFT JOIN 
                  (SELECT img1.idArticulo, 
                                img1.imagen
                      FROM imagenes AS img1
                   INNER JOIN 
                    (SELECT idArticulo,
                                  Min(idImagen) AS MidImagen
                       FROM imagenes
                       GROUP BY idArticulo) AS img2 
                    ON img1.idImagen = img2.MidImagen) AS sbcimagenes 
                ON articulos.idArticulo = sbcimagenes.idArticulo) 
                LEFT JOIN (SELECT estadisticas.idarticulo,
                                               SUM(estadisticas.visitas) as SuVisitas
                                      FROM estadisticas
                                      GROUP BY estadisticas.idarticulo) AS SbCVisitas 
                 ON articulos.idArticulo = SbCVisitas.idarticulo;
 

Creo!! Vaya seguro.

Crea una imagen llamada noimagen.jpg para los que no tengan!!!

Nota; jurena eres más rápido que yo!!!

[ecarlevaro]

Bueno así parece entonces que jurena es más rápido pero quimfv más efectivo.

Es que probé con ambas consulta luego de hojearlas y la de quimfv en su aparente complejidad funcionó muy bien mientras la de jurena hace lo mismo que la mía. Que por cierto, no devuelve como yo dije al principio la cantidad de registros de imagen de cada idArticulo en la DB sino que devuelve la cantidad de imágenes multiplicada por la cantidad de visitas, así un artículo con 6 imágenes y 335 visitas da 2010 "visitas".

Me acabo de desayunar el tema de las subconsultas en el Manual de MySQL para entender la consulta de quimfv, que si anda con ganas y tiempo le agradecería me explicara al menos a grandes rasgos, como fue razonando él para llegar a la consulta que llegó. Por mi parte entiendo esto:

Código SQL:

/*Las columnas que quiero*/
SELECT articulos.idarticulo, 
       articulos.titulo, 
       sbcimagenes.src AS img, 
       SbCVisitas.SuVisitas AS Visitas
FROM (articulos 
               LEFT JOIN 
                  /*Consigo todos los articulos y src de la imagen*/
                  (SELECT img1.idArticulo,img1.src FROM imagenes AS img1
                   INNER JOIN 
                    /*El menor idimg por cada articulo*/
                    (SELECT idarticulo,MIN(idimg) AS MidImagen FROM imagenes GROUP BY idarticulo) AS img2 
                    /*Las uno por idimg y consigo todos los articulos con sus imagenes*/
                    ON img1.idimg = img2.MidImagen) AS sbcimagenes 
                 /*Uno por idarticulo con articulos*/
                ON articulos.idarticulo = sbcimagenes.idarticulo) 
                /*Combino las imagenes y los idarticulos con las estadisticas */
                LEFT JOIN (SELECT estadisticas.idarticulo,SUM(estadisticas.visitas) AS SuVisitas
                                      FROM estadisticas
                                      GROUP BY estadisticas.idarticulo) AS SbCVisitas 
                                      /*A través de idarticulo*/
                 ON articulos.idarticulo = SbCVisitas.idarticulo;
 

Lo que también me gustaría entender es por qué falla la consulta mía o la de jurena, ¿por qué cuando le digo que SUM(estadisticas.visitas) multiplica la cantidad de registros de imagen por la cantidad de visitas?.

Bueno ando preguntón y disculpas si molesta, por mi parte las enormes gracias a quimfv porque creo que jamás hubiera llegado a tal consulta.

[quimfv]

Veo que has entendido perfectamente la consulta. Parece compleja pero no lo es.

Para resolver una consulta de este tipo debes aplicar el principio de "divide y vencerás".

Tu pedías

1. Una lista de todos los artículos --> artículos LEFT JOIN ...
2. Una imagen por articulo, concretamente, la imagen con el id mas pequeño de cada articulo. Aquí hay que hacer una doble subconsulta, primero obtener el id de imagen mas pequeño para cada articulo y a partir de este el nombre de la imagen. --> idarticulo,imagen FROM imagenes INNER JOIN (MIN(idArticulo) From imagenes GROUP BY idarticulo) ON idimagen=MIN(idimagen)
Con el INNER JOIN consigo que solo me de el idArticulo y nombreimagen de los idMinimos, si un articulo no tiene imagen no sale en esta subconsulta
3.Un número de visitas por artículo. Estadísticas por articulo --> idarticulo,SUM(visitas) FROM estadistica GROUP BY idarticulo, si un articulo no tiene visitas no sale en esta subconsulta

Como en los tres puntos tengo idArticulo es fácil armar la consulta final jugando con los alias y con LEFT JOIN para que aparezcan todos los artículos de la tabla artículos independientemente de si tienen o no imagen y/o visitas.

Finalmente los IFNULL() tienen una función estética.

Cada una de las partes se pueden ejecutar independientemente, luego es fácil comprobar si dan lo que buscas


Por que falla la vuestra

La cardinalidad de las tablas no es la misma

TablaArticulos tiene n registros (los queremos todos)
LEF JOIN (correcto)
TablaImagenes tiene x imágenes * m artículos (no coincide ni el numero de artículos, además pueden estar repetidos) digamos que tiene i registros
LEFT JOIN
TablaEstadisticas tiene y fechas * m artículos (lo mismo) digamos que tiene e registros

luego la consulta nos dará potencialmente i*e registros todos los de artículos combinados por idart con TODOS los de imágenes (i) y estos combinados con TODOS los de estadísticas (e). Si ademas n fuera mayor que el mayor de i o e tendriamos que sumar el numero de articulos restante.

Como hacéis el group by se reduce a n registros, el MIN(idimagen) funciona puesto que el mínimo es el mínimo pero el SUM(visitas) suma todas las repeticiones que haya (i*e).

Por otro lado intentáis obtener el nombre de la imagen directamente, eso es imposible ya que sql nos puede dar el mínimo de un grupo dado pero no los campos que lo acompañan...(MsAccess (no se si MsSQLserver) implementa FIRST() y LAST() que podían haber servido pero no es SQL estándar).

Fijate que la cardinalidad de mis subconsultas tiene como máximo n registros y sin repeticiones.


FINALMENTE Y EN MAYÚSCULAS ESTA NO ES LA ÚNICA SOLUCIÓN Y NO SE SI DE TODAS LAS POSIBLES ES LA MAS EFICIENTE

[ecarlevaro]

¡Sí! ¡Estoy entendiendo!

Todavía me falta para aprehender este método de divide y vencerás y las subconsultas, pero con tu ayuda y lo que estuve leyendo estoy aprehendiendo la esencia de consultas a mútliples tablas y combinar las tablas.

Y ese concepto de la cardinalidad lo tenía bastante olvidado de cuando alguna vez una profesora de matemática me habrá explicado teoría de conjuntos, pero ya me lo repasé.

¡Muchas gracias quim!

Ahora jugaré con EXPLAIN para ir viendo si puedo optimizar esta consulta.

Nuevamente, gracias.

[quimfv]

Si en la teoria de conjuntos esta la base de todo.

método de divide y vencerás

mas que un método es un principio aplicable a la programación en general.

Quim