[SOLUCIONADO] Join 4 tablas

[alpe2000]

Saludos cordiales,
Tengo 4 tablas: (pongo los campos relevantes)

Productos
producto_id,descripcion,activo

Productos_a_subcategias
id,producto_id,subcategoria_id

comentarios
id,producto_id

votos
id,producto_id

Lo que hago ahora:
Selecciono todos los productos de la tabla productos que esten condicionados con la subcategoria a la que pertenece

Código MySQL:

SELECT * FROM productos p 
INNER JOIN productos_a_subcategorias pas ON p.producto_id = pas.producto_id 
WHERE pas.subcategoria_id = 27 AND p.activo = TRUE
 

Despues, pues en programacion, recorro el recordset y para cada uno de los productos, hago la media de los votos:

Código MySQL:

SELECT AVG(voto) as media_votos FROM valoracion_producto WHERE producto_ID = 1
 

Y tambien cuento la cantidad de comentarios que tiene cada uno:

Código MySQL:

Select COUNT(*) from opiniones Where id_producto = 1
 

Esto creo que es malgastar recursos del servidor, ademas de que asi, no puedo ordenar los resultados, por ejemplo, por producto mas comentado o mas valorado.

Y he probado haciendo algunas pruebas con INNER JOIN, LEFT JOIN, pero mis conocimientos son basicos y no me aclaro.
Esto es lo que llevo ahora, pero no funciona bien :(

Código MySQL:

SELECT * FROM productos p 
INNER JOIN productos_a_subcategorias pas ON p.producto_id = pas.producto_id AND pas.subcategoria_id = 27 AND p.activo = TRUE 
LEFT JOIN (SELECT id_producto opi_id_producto,activo opi_act,COUNT(*) total_opiniones FROM opiniones ) opi ON p.producto_id = opi.opi_id_producto AND opi.opi_act = TRUE 
LEFT JOIN (SELECT producto_id voto_producto_id,AVG(voto) as voto_media_votos FROM valoracion_producto) voto ON p.producto_id = voto.voto_producto_id
 

Alguna ayuda me vendrá genial, llevo dos dias haciendo pruebas, pero no doy con la tecla.

[leonardo_josue]

Hola alpe2000:

En realidad no estás tan alejado de la respuesta, sin embargo tienes algunos detalles a considerar:

1. en las cláusulas ON, es recomendable que sólo utilices aquellas condiciones que involucran a las llaves de las tablas, las condiciones para filtrar la información debes colocarlas en la sección WHERE.
2. Tienes un problema con las subconsultas, ya que NO ESTÁS AGRUPANDO POR PRODUCTO, por lo tanto, tanto el promedio como el conteo están arrojando un resultado general, no particular para un producto.

No es lo mismo poner esto:

Código MySQL:

SELECT id_producto opi_id_producto, activo opi_act, COUNT(*) total_opiniones 
FROM opiniones
 

a poner esto:

Código MySQL:

SELECT id_producto opi_id_producto, activo opi_act, COUNT(*) total_opiniones 
FROM opiniones
GROUP BY id_producto opi_id_producto, activo opi_act
 

3. Evita el uso de SELECT *, esto tiene un pésimo rendimiento... aun cuando necesites TODOS los campos de una tabla (que es un caso poco común), es mejor que listes todos y cada uno de los campos.

El resto parece estar en orden, pero veamos si te queda más claro con este ejemplo.

Supongamos que tienes las siguientes tablas:

Código MySQL:

mysql> SELECT * FROM productos;
+-------------+-----------------+
| id_producto | descripcion     |
+-------------+-----------------+
|           1 | producto uno    |
|           2 | producto dos    |
|           3 | producto tres   |
|           4 | producto cuatro |
+-------------+-----------------+
4 rows in set (0.00 sec)
 
 
mysql> SELECT * FROM votos;
+---------+-------------+------+
| id_voto | id_producto | voto |
+---------+-------------+------+
|       1 |           1 |   10 |
|       2 |           1 |    9 |
|       3 |           1 |   10 |
|       4 |           2 |    7 |
|       5 |           2 |    9 |
+---------+-------------+------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM comentarios;
+---------------+-------------+----------------+
| id_comentario | id_producto | comentario     |
+---------------+-------------+----------------+
|             1 |           1 | comentario 1.1 |
|             2 |           1 | comentario 1.2 |
|             3 |           3 | comentario 3.1 |
+---------------+-------------+----------------+
3 rows in set (0.00 sec)
 

es decir, tienes cuatro productos:

• el producto UNO tiene comentarios y votos
• el producto DOS solo tiene votos, pero no tiene comentarios
• el producto TRES solo tiene comentarios, pero no tiene votos
• el producto CUATRO no tiene ni comentarios ni votos

Ahora, para obtener el PROMEDIO de votos y el CONTEO de comentarios, tienes que hacer uso de las funciones de agrupación PERO UTILIZAR EL GROUP BY:

Código MySQL:

mysql> SELECT id_producto, AVG(voto) promedio_votos
    -> FROM votos
    -> GROUP BY id_producto;
+-------------+----------------+
| id_producto | promedio_votos |
+-------------+----------------+
|           1 |         9.6667 |
|           2 |         8.0000 |
+-------------+----------------+
2 rows in set (0.00 sec)
 
mysql> SELECT id_producto, COUNT(id_producto) total_comentarios
    -> FROM comentarios
    -> GROUP BY id_producto;
+-------------+-------------------+
| id_producto | total_comentarios |
+-------------+-------------------+
|           1 |                 2 |
|           3 |                 1 |
+-------------+-------------------+
2 rows in set (0.00 sec)
 

Entonces si, puedes hacer los LEFT JOIN'S para asociar estas cantidades a sus productos:

Código MySQL:

mysql> SELECT p.id_producto, p.descripcion, v.promedio_votos, c.total_comentarios
    -> FROM productos p
    -> LEFT JOIN ( SELECT id_producto, AVG(voto) promedio_votos
    ->             FROM votos
    ->             GROUP BY id_producto) v ON v.id_producto = p.id_producto
    -> LEFT JOIN ( SELECT id_producto, COUNT(id_producto) total_comentarios
    ->             FROM comentarios
    ->             GROUP BY id_producto) c ON c.id_producto = p.id_producto;
+-------------+-----------------+----------------+-------------------+
| id_producto | descripcion     | promedio_votos | total_comentarios |
+-------------+-----------------+----------------+-------------------+
|           1 | producto uno    |         9.6667 |                 2 |
|           3 | producto tres   |           NULL |                 1 |
|           2 | producto dos    |         8.0000 |              NULL |
|           4 | producto cuatro |           NULL |              NULL |
+-------------+-----------------+----------------+-------------------+
4 rows in set (0.00 sec)
 

Haz los ajustes que te comento y si continuas con problemas, postea algunos datos de ejemplo de tus tablas y dinos en qué está fallando tu consulta.

Saludos
Leo

[alpe2000]

:D :D :D

Todo perfecto EL GROUP BY era la clave, mis pobres conocimientos no llegaban, jajjaja. Muchas gracias.
Así me quedó la sentencia despues de agregar la tabla Productos_a_subcategias

Código MySQL:

SELECT p.producto_id, p.descripcion, p.precio, p.referencia, p.vistojsg, v.promedio_votos, c.total_comentarios FROM productos p 
LEFT JOIN ( SELECT producto_id, AVG(voto) promedio_votos FROM valoracion_producto GROUP BY producto_id) v 
    ON v.producto_id = p.producto_id 
LEFT JOIN ( SELECT id_producto, COUNT(id_producto) total_comentarios FROM opiniones GROUP BY id_producto) c 
    ON c.id_producto = p.producto_id 
INNER JOIN productos_a_subcategorias pas ON p.producto_id = pas.producto_id 
WHERE pas.subcategoria_id = "& subcategoria_id &" AND p.activo = TRUE 
ORDER BY " & session("orden")
 

Le paso las variables de subcategoria segun la subcategoria que quiera mostrar y el orden segun las preferencias del usuario, y todo funciona correcto.
MIL GRACIAS