Join de 3 tablas y COUNT en 1

[djmashe]

Saludos, tengo 3 tablas con los siguientes campos:

• Tabla te01_conductores: id_conductor (PK), te09_id_municipio (FK de te09_municipios), …
• Tabla te09_municipios: id_municipio (PK), municipio (varchar), …
• Tabla te10_conductores_inhabilitados: id_inh (PK), id_conductor (FK de te01_conductores), vigencia (boolean), …

La relación está dada por:

Código:

municipios --> conductores <-- inhabilitados

La idea es listar el total de conductores inhabilitados por municipio (que depende del campo vigencia), pero mostrando en el caso que el municipio no tenga, el valor ‘0’.

Si realizo la siguiente consulta puedo obtener todos los municipios con o sin valores en el campo cantidad:

Código SQL:

SELECT te09_municipios.municipio, COUNT(te10_conductores_inhabilitados.vigencia) AS cantidad
FROM te01_conductores
 LEFT OUTER JOIN te10_conductores_inhabilitados ON (te01_conductores.id_conductor = te10_conductores_inhabilitados.id_conductor)
 RIGHT OUTER JOIN te09_municipios ON (te01_conductores.te09_id_municipio = te09_municipios.id_municipio)
GROUP BY te09_municipios.municipio
ORDER BY  cantidad;
 

lo cual da como resultado (75 registros):

Código:

municipio			cantidad
----------------------------------------
General Urquiza			0
Corpus				0
Colonia Delicia			0
Arroyo del Medio		0
Mojón Grande			0
9 de Julio			0
Azara				0
Santa María			0
Los Helechos			0
Florentino Ameghino		0
Itacaruaré			0
O. V. Andrade			0
Almafuerte			0
San José			0
Candelaria			0
Santo Pipó			0
Apóstoles			0
Leandro N. Alem			0
Profundidad			0
Fachinal			0
Cerro Corá			0
Loreto				0
General Alvear			0
Panambi				0
Caá Yarí			0
Gobernador López		0
San Ignacio			0
Dos de Mayo			0
San Javier			0
Dos Arroyos			0
Guaraní				0
Bonpland			0
Puerto Piray			1
Colonia Victoria		1
Santiago de Liniers		1
Caraguatay			1
San Antonio			1
Colonia Polana			1
Puerto Leoni			1
Santa Ana			1
Mártires			1
El Soberbio			1
Colonia Aurora			1
Tres Capones			1
Garuhapé			1
Cerro Azul			1
Puerto Libertad			2
El Alcázar			2
Gobernador Roca			2
Ruiz de Montoya			2
Colonia Wanda			2
Campo Viera			2
San Martín			2
Bernardo Irigoyen		2
25 de Mayo			2
Hipólito Irigoyen		2
C. de la Sierra			2
Alba Posse			2
Campo Grande			2
Montecarlo			3
Aristóbulo del Valle		3
Campo Ramón			3
Cte. Andresito			3
Colonia Alberdi			3
San Pedro			4
Garupá				4
San Vicente			4
Puerto Esperanza		5
Capioví				5
Jardín América			7
Puerto Rico			13
Puerto Iguazú			19
Posadas				24
Oberá				28
Eldorado			34

Pero si ha dicha consulta la modifico agregando un WHERE que condicione el campo vigencia, no me muestra las filas con valor 0. O sea:

Código SQL:

SELECT te09_municipios.municipio, COUNT(te10_conductores_inhabilitados.vigencia) AS cantidad
FROM te01_conductores
 LEFT OUTER JOIN te10_conductores_inhabilitados ON (te01_conductores.id_conductor = te10_conductores_inhabilitados.id_conductor)
 RIGHT OUTER JOIN te09_municipios ON (te01_conductores.te09_id_municipio = te09_municipios.id_municipio)
WHERE te10_conductores_inhabilitados.vigencia = TRUE
GROUP BY te09_municipios.municipio
ORDER BY  cantidad;
 

da como resultado (32 registros):

Código:

municipio			cantidad
----------------------------------------
Puerto Piray			1
Colonia Victoria		1
Santiago de Liniers		1
Caraguatay			1
San Antonio			1
Colonia Polana			1
Puerto Leoni			1
Santa Ana			1
Mártires			1
El Soberbio			1
Colonia Aurora			1
Tres Capones			1
Garuhapé			1
Cerro Azul			1
Puerto Libertad			2
El Alcázar			2
Gobernador Roca			2
Ruiz de Montoya			2
Colonia Wanda			2
Campo Viera			2
San Martín			2
Bernardo Irigoyen		2
25 de Mayo			2
Hipólito Irigoyen		2
C. de la Sierra			2
Alba Posse			2
Campo Grande			2
Montecarlo			3
Aristóbulo del Valle		3
Campo Ramón			3
Cte. Andresito			3
Colonia Alberdi			3
San Pedro			4
Garupá				4
San Vicente			4
Puerto Esperanza		5
Capioví				5
Jardín América			7
Puerto Rico			13
Puerto Iguazú			19
Posadas				24
Oberá				28
Eldorado			34

• ¿Hay alguna manera de realizar el WHERE para filtrar el campo vigencia pero devolviendo tambien las filas en municipio que no tengan datos?
• ¿Están mal los joins?

Ya probé CASE WHEN y me dió el mismo resultado.

Gracias desde ya a quien pueda dar una mano...

[djmashe]

Quiero agradecer a jurena, de php-hispano, quien me aportó la respuesta.

La verdad que es todo un tema entender las subconsultas dentro de los FROM (eso me había planteado huesos52) y de los JOINs ahora (planteado por jurena).

Por lo pronto dejo a mano el script con la solución y adaptado a mis necesidades:

Código SQL:

SELECT municipio, COALESCE(subconsulta.total,0) AS cantidad
FROM te09_municipios AS M 
 LEFT JOIN (SELECT C.te09_id_municipio AS IDm, COUNT(vigencia) AS total 
            FROM te01_conductores AS C 
             INNER JOIN te10_conductores_inhabilitados AS I ON (C.id_conductor = I.id_conductor)
            WHERE (vigencia = TRUE) 
            GROUP BY IDm) AS subconsulta ON (M.id_municipio = subconsulta.IDm)
ORDER BY municipio;
 

Saludos.

[huesos52]

Me alegra djmashe. En este foro también tenemos el placer de contar con la colaboración de jurena. sin duda una respuesta memorable.

saludos