Cómo hacer una consulta de referencia cruzada.

[boy-computer]

Tengo 3 tablas. con los nombres: municipios, productos, productos_municipio

municipios:
cod_municipio --------------- char(2)
cod_provincia ---------------- char(2)
municipio --------------------- char(40)
cantidad_trabajadores--- char(3)

prodcutos:
cod_prod ---------------- int(2)
disponible -------------- enum('si','no')
producto --------------- char(50)
cantidad_productos--- char(5)

productos_municipio:
cod_prod_mpio --------------- int(4)
mes ------------------------------ char(30)
cod_municipio --------------- char(2)
cod_prod ---------------------- int(2)
cantidad_productos_municipio ------ char(4)

Quiero hacer una consulta que me devuelva el siguiente resultado:

MUNICIPIOS ---- CANTIDAD_TRABAJADORES ------ PRODUCTO1 ------ PRODUCTO2
municipio1 ----------- 10 ---------------------------------- 15 Kg --------------- 20 Kg
municipio2 ----------- 10 ---------------------------------- 45 Kg -------------- 2 Kg
municipio3 ----------- 15 ---------------------------------- 5 Kg ----------------- 41 Kg

total -------------------suma1 -------------------------- suma2 -------------------------suma3

esta consulta la he hecho en ACCESS y me da lo que deseeo, pero cuando la llevo a MySQL a trav'es del phpmyadmin pero me devuelve el siguiente error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSFORM Last(productos_municipio.cantidad_productos_munici pio) AS UltimoDecantidad_prod_' at line 1

Cómo puedo resolver este problema y obtener una consulta de referencia cruzada en MySQL.

[huesos52]

Boy Computer

MySQL no soporta referencias cruzadas como tal.
Una salida similar, es
select *from tabla \G
Pero no se si te sirva de esta forma.

Cuentanos como te va.

[gnzsoloyo]

Se puede obtener con subconsultas con agrupamientos usando WITH ROLLUP.
Hay que probar:

Código sql:

SELECT 
    municipio, 
    SUM(IFNULL(T1.cantidad_trabajadores),0) Trabajadores, 
    SUM(IFNULL(T2.cantidad_productos_municipio),0) Producto1, 
    SUM(IFNULL(T3.cantidad_productos_municipio),0) Producto2
FROM municipio T1 LEFT JOIN productos_municipio T2 USING(cod_municipio)
          LEFT JOIN productos_municipio T3 USING(cod_municipio)
WHERE T23.cod_prod=1 OR T3.cod_prod=2
GROUP BY municipioWITH ROLLUP;
 

Prueba y veremos si estamos un poco más cerca.

[boy-computer]

Me da el siguiente error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '),0) Trabajadores, SUM(IFNULL(T2.cantidad_productos_municipio),0) Prod' at line 3

Addemás gnzsoloyo agradecería, que me explicaras un poco esa consulta y de esa forma entenderé lo que debo obtener con ella y así sabreé explicar mejor lo que quiero y que me esta dando.

[jurena]

El error es en realidad una errata al teclear; prueba así:
SELECT
municipio,
SUM(IFNULL(T1.cantidad_trabajadores,0)) Trabajadores,
SUM(IFNULL(T2.cantidad_productos_municipio,0)) Producto1,
SUM(IFNULL(T3.cantidad_productos_municipio,0)) Producto2
FROM municipio T1 LEFT JOIN productos_municipio T2 USING(cod_municipio)
LEFT JOIN productos_municipio T3 USING(cod_municipio)
WHERE T2.cod_prod=1 OR T3.cod_prod=2
GROUP BY municipio WITH ROLLUP;

[boy-computer]

Colega, casi casi, pero en la consulta hay un detalle que no me conviene y es que tengo que ir declarando los productos, necesito que sea capaz de crearme tantas columnas de producto como existan

[gnzsoloyo]

No existe una forma de hacer esa consulta directamente con SQL. En ese caso tienes que construir parte de la sentencia en forma dinámica a través de la aplicación (PHP, ASP o lo que sea), por cuanto lo que estás tratando de construir es un cubo OLAP rudimentario.
Ese tipo de análisis se realiza con herramientas bastante elaboradas, o bien, diseñando consultas ad-hoc.
En este caso, para que se entienda, no se puede definir una consulta única, por cuanto para devolverte una columna por producto, debe realizarse un LEFT JOIN con alias sobre la misma tabla por cada producto de la lista, y como la cantidad puede ser variable, las combinaciones pueden volverse infinitas.
En realidad ningún DBMS te permite hacer esas consultas directamente, simplemente porque para ello existen las herramientas OLAP.

[boy-computer]

Ok, colega voy a estudiar lo que me dices y después les cuento, a ver que logro hacer.