problema con recuento de registros

[aniMAYtions]

Hola amig@s.
Mi duda surge a la hora de contar los registros de una tabla agrupados por 2 campos y coincidentes con otras 2 tablas externas, contando también los que no tienen registros como 0.
Me explico mejor. Tengo una tabla 'servicios' que tiene, entre otre otros, 3 campos clave para mi consulta. Estos son empresa, caracter y tipo.
Resulta que yo soy el coordinador regional de esta empresa y en mi comunidad tengo varias empresas a mi cargo. Hay varios tipos de servicios que ofrecemos y estos servicios pueden ser de 2 caracteres, públicos o privados.
Como ya os he dicho, yo llevo varias empresas, pero me encuentro situado en una(tecnico, id_empresa), aunque esta empresa pertenece a la comunidad autónoma que abarca todas.
Ahora, debería saber el número de servicios registrados de cada tipo y caracter sólo de las empresas de mi ámbito.
Algo como ésto:

Código SQL:

tipos | Caracter | COUNT(*)
------------------------------------------
tipo1 | Público    |
-------------------------------------------
tipo1 | Privado   |
------------------------------------------
tipo2 | Público    |
-------------------------------------------
tipo2 | Privado   |
---------------------------------------------
tipo3 | Público    |
-----------------------------------------------
tipo3 | Privado   |
 

Ahí deben aparecer todos los tipos de la tabla tipos con los 2 caracteres, hayan o no servicios relacionados.

My consulta en estos momentos es

Código SQL:

SELECT nombre_tipo, nombre_caracter, COUNT(*) FROM servicios s(v), tipos t(tv), caracte c(cv)  WHERE s.id_tipo = t.id_tipo AND s.id_caracter = c.id_caracter AND s.id_empresa IN(SELECT e.id_empresa FROM empresas e WHERE id_comunidad IN(SELECT id_comunidad FROM empresas e, tecnicos t WHERE e.id_empresa = t.id_empresa AND id_tecnico = '$id_tec')) AND s.id_caracter IS NOT NULL
GROUP BY   s.id_tipo, s.id_caracter
 

Esto me arroja sólo el resultado de los tipos de los que hay servicios realizados, pero los otros nada. Y lo que me gustaría sería listar todos los tipos, los caracteres por tipo y el número de servicios aunque sea 0.

Me he explicado bien? Espero no haberos aburrido.
Un saludo y gracias!!

[Heimish2000]

Con un left join es muy facil.

En cualquier caso, mi recomendacion es cuando unas tablas, utilizar sentencias JOIN en vez de unirlas directamente

Código SQL:

SELECT nombre_tipo, nombre_caracter, COUNT(*) 
FROM servicios s(v)
LEFT JOIN tipos t(tv) ON 
s.id_tipo = t.id_tipo
LEFT JOIN caracte c(cv)  ON
s.id_caracter = c.id_caracter
WHERE  s.id_empresa IN (SELECT e.id_empresa 
FROM empresas e 
WHERE id_comunidad IN
(SELECT id_comunidad 
FROM empresas e, tecnicos t 
WHERE e.id_empresa = t.id_empresa 
AND id_tecnico = '$id_tec')) 
AND s.id_caracter IS NOT NULL
GROUP BY s.id_tipo, s.id_caracter
 

[aniMAYtions]

Muchas gracias por tu respuesta Heimish2000, pero resulta que me han exigido mostrar los datos de otra forma y eso no voy a poder conseguirlo con una sola consulta. El nuevo requerimiento es este:

Código SQL:

tipos | Público   | Privado
      ------------------------------------------
      tipo1 | COUNT(*)| COUNT(*)
      -------------------------------------------
      tipo2 | COUNT(*)| COUNT(*)
      ------------------------------------------
      tipo3 | COUNT(*)| COUNT(*)
      -------------------------------------------
      tipo4 | COUNT(*)| COUNT(*)
      ---------------------------------------------
      tipo5 | COUNT(*)| COUNT(*)
      -----------------------------------------------
      tipo6 | COUNT(*)| COUNT(*)
 

Así que lo que he hecho es, en primer lugar, listar los tipos y mientras los voy mostrando con un while en php coloco en el primer td el tipo, para el segundo td hago otra consulta y cuento el número de servicios de ese tipo que sean públicos y para el tercer td repito la consulta anterior cambiando el id_caracter por 2.
Es la única solución que se me ocurre por el momento.
Pero ya por tener algún conocimiento más sobre sql(olvidé el uso de left y right join), habría alguna consulta para obtener los resultados en este orden?

Saludos y gracias de nuevo.

[Heimish2000]

Seria algo así (yo te doy la idea, la solución exacta tienes que sacarla tu)

Código SQL:

SELECT TIPO, SUM(PUB), SUM(PRI)
FROM
(
SELECT TIPO, CASE WHEN CARACTER = 'Público' THEN '1' ELSE '0' END AS PUB,
CASE WHEN CARACTER = 'Privado' THEN '1' ELSE '0' END AS PRI
FROM TIPOS
)
GROUP BY TIPO
 

[aniMAYtions]

Pues chic@, no sé cómo estaré esta mañana que no me sale la consulta.
Me da el error #1248 - Every derived table must have its own alias .
Pero bueno, me exigen rapidez y he optado por hacerlo de la otra forma que se me ocurrió más pronto.
Aunque me gustaría, para optimizar la aplicación, aplicar el cambio que me has sugerido.

Saludos y buen dia!!

[Heimish2000]

Código SQL:

SELECT TIPO, SUM(PUB), SUM(PRI)
FROM
(
SELECT TIPO, CASE WHEN CARACTER = 'Público' THEN '1' ELSE '0' END AS PUB,
CASE WHEN CARACTER = 'Privado' THEN '1' ELSE '0' END AS PRI
FROM TIPOS
) AS MITABLA
GROUP BY TIPO
 

[aniMAYtions]

Muchas gracias de nuevo. Pero sigo intentando que me salgan todos los tipos aunque no haya registros coincidentes en la tabla sservicios y no lo logro!!
Siento ser pesada, pero a ver si pudes darme una solucion..
Gracias!!

[gnzsoloyo]

A mi entender el problema de la consulta es la lógica con la que se está elaborando.
En el primer post dice que desea saber cuál es la cantidad y tipos de servicios brindados a las empresas de la zona asignada, aunque esa cantidad sea cero, pero está basando la consulta en los servicios efectivamente brindados. Eso no está bien. SI lo que que se desea es conocer la cantidad y tipo de servicios brindados a las empresas cliente, la primer tabla que contiene todas las posibilidades no es servicio, es empresas.
Es decir, que lo primero a determinar es cuáles son las empresas del ámbito que se analizarán:

Código MySQL:

SELECT id_empresa
FROM empresas e
WHERE id_comunidad = valorbuscado;
 

De esas empresas, hay que averiguar las que tuvieron servicios, publicos o privados:

Código MySQL:

SELECT 
    e.id_empresa, IFNULL(s.id_servicio,0) id_servicio
FROM 
    empresas e 
    LEFT JOIN servicio s ON s.id_empresa = e.id_empresa
WHERE e.id_comunidad = valorbuscado;
 

Esta tabla devolverá todas los id de empresas, y NULL donde no haya servicios, entonces hay que determinar qué tipos de servicio fueron aquellos que fueron válidos, pero en vez de contarlos, hay que sumarlos para que pueda controlarse en NULL:

Código MySQL:

SELECT 
    e.id_empresa, 
    SUM(IF(c.caracter = 'Público',1, 0)) Publico,
    SUM(IF(c.caracter = 'Privado',1, 0)) Privado
FROM 
    empresas e 
    LEFT JOIN servicio s ON s.id_empresa = e.id_empresa
    INNER JOIN  caracte c ON s.id_caracter = c.id_caracter
WHERE e.id_comunidad = valorbuscado
GROUP BY e.id_empresa;
 

Aquí hay algunas observaciones a realizar:
- Si el carácter del servicio es un valor binario (de sólo dos valores posibles), crear una tabla para administrarlo, es innecesario. Con un campo BIT o BOOL alcanzaría si predefines el estado; e incluso con un ENUM sería suficiente.
- Si el id_caracter está contenido en la tabla servicio, no se necesita en la tabla tipo, o al menos se vuelve superfluo. Si lo vas a mantener en la tabla tipo, hay que eliminarlo de servicio porque se vuelve redundante, y allí sí se requiere incluir la tabla tipo. en el INNER JOIN
- Cruzar la tabla de técnicos no se requiere en tu consulta, porque no estás listando los técnicos, sólo los tipos de servicio, y para ello saber qué técnico lo realizó es irrelevante. No aporta ni saca nada, por lo tanto el JOIN con esa tabla se vuelve superfluo.