Greetings to all, I have a problem when making a query that basically is that I need to know the number of employees per city. The result is like this.
It seems to me that the error is because in the employee table it does not make reference to the location table, but to the department table, so there would have to be an intermediate reference between location and department but I cannot find a way to do it
This is the code I tried
SELECT u.ciudad AS 'Ciudad',
COUNT(e.idempleado) AS 'Cantidad de empleados'
FROM RH..ubicacion AS u
left join RH..empleado AS e
ON u.idubicacion = e.idempleado
GROUP BY u.ciudad;
go
Thank you very much for your help
As you say, you have to join
empleado
withdepartamento
and this withubicacion
using the ids that serve as foreign and primary keys respectively. For example the employee id is useless here for JOINs, because that column is not related to the other tables.Also check the
GROUP BY
, in certain contexts there may be several different cities that have the same name (for example if there is the possibility of different countries or regions), that is why I have also grouped by the id.This is how it should work:
If you only want to show the cities that have employees, change them
LETF JOIN
toINNER JOIN
.