Puedo hacer un doble group by utilizando una subconsulta?

[oscarbt]

Buenas a todos, necesito hacerle una consulta a una base de datos que consiste en lo siguiente:

Mostrar todas las empresas agrupadas por ciudad, es decir que me salga:

Ciudad 1 Empresa 1
Ciudad 1 Empresa 2
Ciudad 1 Empresa 3
Ciudad 2 Empresa 4
Ciudad 2 Empresa 5

O ciudad 1 Empresa 1 - Empresa 2 - Empresa 3

Hasta aca la consulta la hago bien, pero ademas de esto, debo mostrar el listado de personas que trabajan en cada una de las empresas, entonces asumo que la consulta me debe arrojar los resultados como este:

Ciudad 1 Empresa 1 Persona 1
Ciudad 1 Empresa 1 Persona 2
Ciudad 1 Empresa 1 Persona 3
Ciudad 1 Empresa 2 Persona 4

Y no he podido hacer la consulta para este caso:
Las tablas que manejo son:

TABLA CIUDAD

Código SQL:

/* Table: CIUDAD                                             */
 
CREATE TABLE EMPRESA
(
   COD_CIU              INT NOT NULL,
   NOM_CIU         VARCHAR(30),
   
   PRIMARY KEY (COD_CIU)
);
 

TABLA DE EGRESADOS

Código SQL:

/* Tabla: EGRESADO (PERSONAS)                                              */
 
CREATE TABLE EGRESADO
(
   NUM_CED_EGR          INT NOT NULL,
   APE_EGR              CHAR(25),
   FEC_NAC_EGR          DATE,
   EMA_EGR              VARCHAR(70),
   PRIMARY KEY (NUM_CED_EGR)
);
 

TABLA EMPRESAS

Código SQL:

/* Table: EMPRESA                                               */
 
CREATE TABLE EMPRESA
(
   NIT_EMP              INT NOT NULL,
   RAZ_SOC_EMP          VARCHAR(50),
   DIR_EMP              VARCHAR(70),
   EMA_EMP              VARCHAR(70),
   REP_LEG_EMP          CHAR(80),
   PRIMARY KEY (NIT_EMP)
);
 

Y una tabla hija que sale de las dos tablas anteriores y la denomino
EMPRESAS_TRABAJO_EGRESADO, donde guardo cada empresa con cada egresado

Código SQL:

/* Table: EMPRESAS_TRABAJO_EGRESADO                             */
 
CREATE TABLE EMPRESAS_TRABAJO_EGRESADO
(
   NUM_CED_EGR          INT NOT NULL,
   NIT_EMP              INT NOT NULL,
   FEC_INI              DATE,
   FEC_FIN              DATE,
   PRIMARY KEY (NUM_CED_EGR, NIT_EMP)
);
 

Y la consulta la hice de la siguiente forma:

Código MySQL:

SELECT c.nom_ciu, e.raz_soc_emp FROM (ciudad c INNER JOIN empresa e USING (cod_ciu)) GROUP BY e.cod_ciu WHERE e.nit_emp IN 
(SELECT eg_nom_egr FROM (egresado eg INNER  JOIN empresas_trabajo_egresado ete USING (num_ced_egr) INNER JOIN empresa USING (nit_emp)) GROUP BY ete.nit_emp
 

Pero no me salen los resultados, como podria hacer esta consulta?

Agradezco me puedan ayudar

[Nano_]

Saludos

Prueba de esta manera

Código MySQL:

SELECT c.nom_ciu, e.raz_soc_emp FROM 
      ciudad c INNER JOIN empresa e USING (cod_ciu)
      WHERE e.nit_emp IN
      (SELECT eg_nom_egr 
      FROM egresado eg INNER  JOIN empresas_trabajo_egresado ete USING (num_ced_egr) INNER JOIN empresa USING (nit_emp) GROUP BY ete.nit_emp)
      GROUP BY e.cod_ciu
 

[oscarbt]

La consulta no arroja ningun resultado, pero tampoco genera error.......

[Nano_]

Saludos

mmmm o prueba de esta manera

Código MySQL:

SELECT c.nom_ciu, e.raz_soc_emp,eg.eg_nom_egr  
  FROM empresa e
  INNER JOIN ciudad c  USING (cod_ciu)
  INNER JOIN empresas_trabajo_egresado ete USING (nit_emp)
  INNER JOIN egresado eg USING (num_ced_egr)
  GROUP BY e.cod_ciu
 

[oscarbt]

No, aun sigue generando error, pero me surge una duda: cuando utilizo el INNER JOIN, debo tener en cuenta el orden de la consulta?

[gnzsoloyo]

Yo a todo el asunto le veo un problema inicial: La tabla "Ciudad" no está relacionada de ninguna forma con las demás, por lo que hacer un INNER JOIN con ella es algo traído de los pelos.
Me explico gráficamente. Este es el modelo según las tablas descriptas:

En este gráfico se puede apreciar que niguna de las otras tablas comparte la clave de la ciudad como FK, por lo que no se puede usar en un INNER JOIN.
Tampoco se puede hacer contra la dirección de la empresa, porque no tienen el mismo dominio: El nombre de la ciudad contiene 50 caracteres y el de la direccion 70.

Si la tabla EMPRESA lleva el código indicado, entonces si.

En cualquier caso lo primero que debes es buscar la relación con las empresas. No con las ciudades, ya que esa es la que te importa:

Código MySQL:

SELECT 
     e.raz_soc_emp,
     eg.ape_egr, 
     eg.nom_egr
FROM empresa e   
     INNER JOIN empresas_trabajo_egresado ete USING (nit_emp)
     INNER JOIN egresado eg USING (num_ced_egr)
ORDER BY  raz_soc_emp, ape_egr;
 

Si esto produce resultados, entonces hay que probar:

Código MySQL:

SELECT 
     c.nom_ciu, 
     e.raz_soc_emp,
     eg.ape_egr, 
     eg.nom_egr
FROM empresa e   
     INNER JOIN empresas_trabajo_egresado ete USING (nit_emp)
     INNER JOIN egresado eg USING (num_ced_egr)
     INNER JOIN ciudad c  USING (cod_ciu)
ORDER BY  raz_soc_emp, ape_egr;
 

Hay que recordar que si no se ha vinculado una empresa con una ciudad, esta consulta no devolverá esa empresa. El INNER JOIN es restrictivo: todos los valores deben existir para que devuelva el registro.
Si existe la posibilidad de que no se haya vinculado el la empresa con la ciudad, debe usarse LEFT JOIN:

Código MySQL:

SELECT 
     c.nom_ciu, 
     e.raz_soc_emp,
     eg.ape_egr, 
     eg.nom_egr
FROM empresa e   
     INNER JOIN empresas_trabajo_egresado ete USING (nit_emp)
     INNER JOIN egresado eg USING (num_ced_egr)
     LEFT JOIN ciudad c  USING (cod_ciu)
WHERE TRUE OR c.cod_ciu IS NULL
ORDER BY  raz_soc_emp, ape_egr;
 

Detalle: Si un campo puede ser NULL no se debe colocar en el ORDER BY o en el GROUP BY porque ninguna de las dos clausulas puede procesar nulos.

[oscarbt]

El campo COD_CIU si esta presenta en la tabla empresa, lo que pasa es que al publicar el tema lo omiti, no me habia fijado en ese error...es decir, la tabla empresa tiene la siguiente estructura:


                           
 
CREATE TABLE EMPRESA
(
 NIT_EMP              int NOT NULL,
COD_CIU int NOT NULL,
   RAZ_SOC_EMP          varchar(50),
   DIR_EMP              varchar(70),
   EMA_EMP              varchar(70),
   REP_LEG_EMP          char(80),
   PRIMARY KEY (NIT_EMP)
);
Y si esta como FK...

[oscarbt]

Usando INNER JOIN, es decir, la segunda consulta:

Código MySQL:

SELECT 
c.nom_ciu, 
e.raz_soc_emp,
eg.ape_egr, 
eg.nom_egr
FROM empresa e INNER JOIN empresas_trabajo_egresado ete USING (nit_emp)
INNER JOIN egresado eg USING (num_ced_egr)
INNER JOIN ciudad c USING (cod_ciu) ORDER BY raz_soc_emp, ape_egr;
 

Me genera un error pero no se si esto se deba al orden de la consulta.
Utilizando la siguiente consulta, pero con operador AND, si me genera los resultados

Código MySQL:

SELECT c.nom_ciu 'Ciudad', e.RAZ_SOC_EMP 'Empresa', GROUP_CONCAT( eg.nom_egr
SEPARATOR  " - " )  'Egresados'
FROM ciudad c, empresa e, egresado eg, empresas_trabajo_egresado ete
WHERE c.cod_ciu = e.cod_ciu
AND e.nit_emp = ete.nit_emp
AND eg.num_ced_egr = ete.num_ced_egr
GROUP BY (
ete.nit_emp
)
 

A que se debe?....Cuando utilizo INNER JOIN, se tiene en cuenta el orden de la consulta?...Muchas gracias

[gnzsoloyo]

Me genera un error pero no se si esto se deba al orden de la consulta.

Tienes que ser más específico: ¿Qué error te genera?
No estamos usando tu base y tu computadora, no podemos acceder a tus datos, por lo que no sabemos a qué error te refieres.

[oscarbt]

Me aparece:

#1052 - Column 'COD_CIU' in from clause is ambiguous

Porque ?

Cuando tu escribes :

Código MySQL:

FROM empresa e
INNER JOIN empresas_trabajo_egresado ete USING (nit_emp)
INNER JOIN egresado eg USING (num_ced_egr)
INNER JOIN ciudad c USING (cod_ciu)
 

estas relacionando ciudad con egresado?...o con empresa?...o con las dos?

[gnzsoloyo]

El tema es simple: Hay más de dos tablas donde existe el campo COD_CIU.
Cuando se da ese tipo de casos es mejor no usar USING sino ON para establecer las relaciones:

Código MySQL:

SELECT 
    c.nom_ciu, 
    e.raz_soc_emp,
    eg.ape_egr, 
    eg.nom_egr
FROM 
    empresa e INNER JOIN 
    empresas_trabajo_egresado ete ON e.nit_emp = ete.nit_emp INNER JOIN 
    egresado eg ON ete.num_ced_egr = eg.num_ced_egr INNER JOIN 
    ciudad c ON e.cod_ciu = c.cod_ciu
ORDER BY raz_soc_emp, ape_egr;
 

En el caso de los JOIN con ON, el orden de las tablas en el ON debe respetar el orden de aparición de las tablas, especialmente en el caso de LEFT y RIGHT JOIN.

Nota: No es buena práctica hacer estas relaciones en el WHERE porque el WHERE no es optimizable y el FORM si. Como el WHERE es lo último que se aplica, ya ha leído todos los registros antes de aplicarlo y bien puede haber generado un producto cartesiano.

[oscarbt]

Muchas gracias por tu ayuda @gnzsoloyo....