Take out 0 with count () Mysql

Navigation
0

I have 2 tables: 

-Tabla departamentos con 1 campo(Nombre_Depart) y 3 inserts:
Comercio,Informatica,Centro.

Tabla Usuarios con 2 campos(Nombre,departamento) y 3 inserts:
Edu en informatica.
Jaime en informatica.
Conserje en centro.

I want to show the TOTAL number of people belonging to the departments and their name on a table. 

SELECT count(Nombre_Depart),Departamento
from usuarios,departamentos
where departamentos.Nombre_Depart=usuarios.Departamento 
GROUP BY Nombre_Depart

The problem is that the commerce department does not show it because in the users table nobody is in that department. What do I have to do to make a 0 - Trade appear?

    
asked by EduBw 30.11.2017 в 16:23
source

2 answers

1

You have to do a left join. 

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

In addition, you should normalize your tables so that in the user table stores the department's id and not its name, so this would be the correct way: 

SELECT d.Nombre_Depart, Count(u.nombre)
from departamentos d
left join usuarios u
on d.id =u.id_Departamento 
GROUP BY d.Nombre_Depart

EDIT: Fixed the return of the Count ()

    
answered by 30.11.2017 / 16:29
source
0

You can make a left join between the two tables so that you do not exclude those who do not have an association LEFT JOIN . 

SELECT count(Nombre_Depart),Departamento
from usuarios LEFT JOIN Departamento ON Departamento.Nombre_Depart = usuarios.departamento 
GROUP BY Nombre_Depart
    
answered by 30.11.2017 в 16:26
Function to add to the click but with values within the parentheses How do I make the PHP file cause AJAX to fail?