Mysql Complex Consultation

Question

Mysql Complex Consultation

Navigation

#1 by (1 votes)

0

I want to show in a table how many people belong to a department and their department head, if there are no people, it will show 0, and if there is no department head assigned, any value ("there is not", 0, I do not care)

In this selection I take the number of people associated with the departments, putting 0 if there is none:

SELECT d.Nombre_Depart as Departamento, Count(u.nombre) as total 
from departamentos d left join usuarios u on d.Nombre_Depart=u.Departamento
GROUP BY d.Nombre_Depart order by total

In this select the heads of the department.

 Select departamento,Nombre,Apellidos
 from usuarios,departamentos,union_pers_rols
 where usuarios.Departamento=departamentos.Nombre_Depart
 and union_pers_rols.Id_usuario=usuarios.Correo 
 and usuarios.Departamento=departamentos.Nombre_Depart
 and union_pers_rols.Id_rol='Jefe Departamento'

The problem is that I do not know how to combine the 2 Selects.

In the Users Table I have the fields: Name, Mail, Department

In the Department Table I have the fields: Nombre_Depart

Users throw the fk to Department

In the Role table I have the fields: type_rol.

In the table Union_pers_rol the fields: user_ID, Id_rol

Union_pers_rol throws the fk of user_ID to users.mail and from Id_rol to the table rol.type_rol

mysql sql

asked by EduBw 02.12.2017 в 23:24

source

1 answer

OpenLayers Map marker coordinates How can I generate an .exe with all the resources internally? (Visual Basic)

score 1 · Accepted Answer

The query would be something like:

select d.nombre_depart,
       coalesce(max(jefe.nombre), 'no hay') as nombre_jefe,
       count(u.nombre) as total
  from departamentos d
  left join usuarios u
    on u.departamento = d.nombre_depart
  left join (
    select u.departamento, u.nombre
      from usuarios u
      join union_pers_rols upr
        on upr.id_usuario = u.correo
       and upr.id_rol = 'Jefe Departamento'
  ) jefe
   on jefe.departamento = d.nombre_depart
 group by d.nombre_depart
 order by total

But as you already know, design has certain problems. Among other things, it is possible to have more than one boss per department. If that happens, the query may return erroneous data.

Actually, it would be best to correct this problem by moving the header information directly in the departamentos table.

In addition, it is always better to establish relationships between tables using numeric ids that can not change. Using names or emails to establish relationships is dangerous because they can change.

Actually, a more correct design would look like the following:

Table usuarios

usuario_id (PK)
departamento_id (FK to departamentos(departamento_id) )
nombre
correo

Table departamentos

departamento_id (PK)
jefe_id (FK to usuarios(usuario_id) )
nombre

Then the query would be:

select d.nombre as nombre_departamento,
       coalesce(jefe.nombre, 'no hay') as nombre_jefe,
       count(u.usuario_id) as total
  from departamentos d
  left join usuarios jefe
    on jefe.usuario_id = d.jefe_id
  left join usuarios u
    on u.departamento_id = d.departamento_id
 group by d.departamento_id, jefe.usuario_id
 order by total