Consulta para tres tablas

[chenteb]

Tengo tres tablas
1a Escuelas
2a Grupos
3a Alumnos

tengo estas tres tablas el id de la tabla escuelas esta en las otras dos (grupos y alumnos), necesito saber lo siguiente.
Una consulta que me de el id de la tabla escuelas y el nombre de la escuela, de la segunda cuantos grupos tiene la escuela y de la tercera cuantos alumnos tiene la escuela, no me interesa saber alumnos x grupo solo con saber cuantos grupos y cuantos alumnos tengo en cada escuela es mas que suficiente he intentado el siguente codigo y no me da el dato que necesito

Código:

SELECT enlbhct.idcct, enlbhct.turno, enlbhct.extens, enlbhct.nombrect, enlbhct.nombremun, COUNT(grupos.idcct) as grupos, COUNT(alumnos.idcct) as alumnos
FROM ((enlbhct LEFT JOIN grupos ON grupos.idcct = enlbhct.idcct)LEFT JOIN alumnos ON alumnos.idcct = grupos.idcct)
GROUP BY enlbhct.idcct

entre otras opciones y nada de nada, comunmente me repite el conteo entre las columnas de numero de grupos y alumnos alguna sugerencia?, gracias saludos

[chenteb]

tambien lo he intentado asi y nada:

Código:

SELECT enlbhct.idcct, count(grupos.idgrupo), count(alumnos.idalumno)
  FROM ((enlbhct LEFT JOIN grupos ON enlbhct.idcct = grupos.idcct)
    LEFT JOIN alumnos ON enlbhct.idcct = alumnos.idcct)
   GROUP BY enlbhct.idcct

el resultado que me de es el numero correcto de grupos pero si alumnos esta en cero, y cuando tengo grupos y alumnos el dato se repite en las dos columnas de la cuenta

[gnzsoloyo]

Partamos de un detalle: LEFT JOIN se usa cuando quieres obtener todos los datos de la primera tabla, tengan o no coincidencias con la segunda. Esto, en tu caso, no parece ser correcto, por cuanto se presupone que todas las escuelas tiene grupos y todas tienen alumnos. En ese contexto, se debería usar INNER JOIN y no LEFT JOIN.
Suponiendo que pueda haber escuelas con cero grupos, el uso de LEFT JOIN podría ser correcto, pero eso lo debes plantear desde el principio.
Ahora bien, para obtener el resultado lo que debes lograr es contar todos los grupos diferentes, y eso no lo estás indicando. Por otro lado, contar la cantidad de alumnos, sin discriminar por grupo, no requiere hacer una consulta combinada, ya que de ese resumen sólo se requieren el ID de la escuela y la cantidad de alumnos.

En otras palabras, debes replantear la lógica de la consulta.

Si esto devuelve los alumnos por escuela:

Código MySQL:

SELECT idcct, COUNT(idalumno) cantAlumnos
FROM  alumnos 
GROUP BY idcct
 

Y esto la cantidad de grupos, suponiendo que los nombres o ID de grupos son iguales entre diferentes escuelas:

Código MySQL:

SELECT E.idcct, COUNT(DISTINCT G.idgrupo) cantgrupos
FROM enlbhct E LEFT JOIN grupos G ON E.idcct = G.idcct
GROUP BY E.idcct
 

Entonces esto debería devolverte lo que quieres:

Código MySQL:

SELECT 
    E.idcct, 
    cantAlumnos, 
    COUNT(DISTINCT G.idgrupo) cantgrupos
FROM 
    enlbhct E 
    LEFT JOIN 
    (SELECT idcct, COUNT(idalumno) cantAlumnos FROM  alumnos GROUP BY idcct) A 
    ON E.idcct = A.idcct 
    LEFT JOIN grupos G 
    ON E.idcct = G.idcct
GROUP BY E.idcct
 

Podría escribirlo así:

Código MySQL:

SELECT 
    E.idcct, 
    COUNT(DISTINCT A.idalumno) cantAlumnos, 
    COUNT(DISTINCT G.idgrupo) cantgrupos
FROM 
    enlbhct E 
    LEFT JOIN 
    alumnos  A ON E.idcct = A.idcct 
    LEFT JOIN 
    grupos G  ON E.idcct = G.idcct
GROUP BY E.idcct
 

Pero no estás aclarando si los ID de alumnos se reinician en cada escuela o son únicos para todas las escuelas, y eso puede afectar el resultado.

[chenteb]

MUCHAS GRACIAS!!!
esto ha solucionado un problema que me traia de cabeza, me has ayudado mucho en verdad muchas gracias.

Dejo una idea de como estan mis tablas como referencia para alguien mas.

ESCUELAS
* idcct

GRUPOS
* idgrupo
* idcct

ALUMNOS
* idalumno
* idgrupo
* idescuela

claro que omito otros campos que son particulares de cada tabla y solo planteo los campos que son comunes entre tablas para su relación, lo que busco es lo siguiente, quiero obtener la lista de mis escuelas todas ellas y en las que ya tienen grupos y alumnos contar los grupos y alumnos, una escuela puede tener o no grupos, los grupos pueden o no tener alumnos, pero no podemos tener alumnos en una escuela sin asignar antes un grupo, entonces lo que quiero es dar seguimiento de las escuelas que ya iniciaron su registro obteniendo su cantidad de grupos y alumnos, espero que haya quedado clara la explicación, gracias nuevamente por tu ayuda gnzsoloyo

[chenteb]

si no es mucha molestia, una duda mas, a partir de la consulta que me muestra cantidad de grupos quiero hacer esto cantGrupos > 0, lo intente usando el WHERE cantGrupos > 0 pero me marca un error(#1054 - Unknown column 'cantgrupos' in 'where clause'), como puedo hacer un filtro a partir del conteo de grupos?, gracias por la ayuda

Iniciado por gnzsoloyo

Partamos de un detalle: LEFT JOIN se usa cuando quieres obtener todos los datos de la primera tabla, tengan o no coincidencias con la segunda. Esto, en tu caso, no parece ser correcto, por cuanto se presupone que todas las escuelas tiene grupos y todas tienen alumnos. En ese contexto, se debería usar INNER JOIN y no LEFT JOIN.
Suponiendo que pueda haber escuelas con cero grupos, el uso de LEFT JOIN podría ser correcto, pero eso lo debes plantear desde el principio.
Ahora bien, para obtener el resultado lo que debes lograr es contar todos los grupos diferentes, y eso no lo estás indicando. Por otro lado, contar la cantidad de alumnos, sin discriminar por grupo, no requiere hacer una consulta combinada, ya que de ese resumen sólo se requieren el ID de la escuela y la cantidad de alumnos.

En otras palabras, debes replantear la lógica de la consulta.

Si esto devuelve los alumnos por escuela:

Código MySQL:

SELECT idcct, COUNT(idalumno) cantAlumnos
FROM  alumnos 
GROUP BY idcct
 

Y esto la cantidad de grupos, suponiendo que los nombres o ID de grupos son iguales entre diferentes escuelas:

Código MySQL:

SELECT E.idcct, COUNT(DISTINCT G.idgrupo) cantgrupos
FROM enlbhct E LEFT JOIN grupos G ON E.idcct = G.idcct
GROUP BY E.idcct
 

Entonces esto debería devolverte lo que quieres:

Código MySQL:

SELECT 
    E.idcct, 
    cantAlumnos, 
    COUNT(DISTINCT G.idgrupo) cantgrupos
FROM 
    enlbhct E 
    LEFT JOIN 
    (SELECT idcct, COUNT(idalumno) cantAlumnos FROM  alumnos GROUP BY idcct) A 
    ON E.idcct = A.idcct 
    LEFT JOIN grupos G 
    ON E.idcct = G.idcct
GROUP BY E.idcct
 

Podría escribirlo así:

Código MySQL:

SELECT 
    E.idcct, 
    COUNT(DISTINCT A.idalumno) cantAlumnos, 
    COUNT(DISTINCT G.idgrupo) cantgrupos
FROM 
    enlbhct E 
    LEFT JOIN 
    alumnos  A ON E.idcct = A.idcct 
    LEFT JOIN 
    grupos G  ON E.idcct = G.idcct
GROUP BY E.idcct
 

Pero no estás aclarando si los ID de alumnos se reinician en cada escuela o son únicos para todas las escuelas, y eso puede afectar el resultado.

[chenteb]

me respondo gracias de todas formas aqui dejo el ejemplo

Código:

    SELECT
        E.idcct,
        COUNT(DISTINCT A.idalumno) cantAlumnos,
        COUNT(DISTINCT G.idgrupo) cantgrupos
    FROM
        enlbhct E
        LEFT JOIN
        alumnos  A ON E.idcct = A.idcct
        LEFT JOIN
        grupos G  ON E.idcct = G.idcct
GROUP BY E.idcct
HAVING cantgrupos > 0

[gnzsoloyo]

La razón es simple: No puedes usar los alias de las columnas en el WHERE, porque el WHERE sólo acepta los nombres reales de las columnas.
Por otro lado, la solución que propones es correcta, ya que el el HAVING sí puede usar los alias, lo mismo que el ORDER BY y el GROUP BY. De todos modos, para lo que quieres hubisese sido suficiente con cambiar el segundo LEFT JOIN por INNER JOIN, ya que el INNER JOIN sólo devuelve registros si existe al menos un registro coincidente, que es precisamente lo que plantea ese HAVING.

Ten en cuenta que el uso de HAVING, GROUP BY y ORDER BY se paga con performance, porque se aplican luego de ejecutar toda la consulta, en cambio el INNER JOIN se produce al leer las tablas, lo que se hace antes que nada.

[chenteb]

Muchas gracias, mas claro ni el agua, con esto entiendo mas el uso de JOIN, HAVING, GROUP BY y ORDER BY muy agradecido.