Add a zero record when count is 0

Question

I want to list BR, BRANCHNAME and the number of people employed in it. There are 5 branches it total and only 4 of them have people employed in it; Branch 05 has no employees in it. After using the following code, the branch 05 will not be shown as the row of branch 05 will not be included after the where statement. I want to show a row of "05 Br05 0".

SELECT EMPLOYEE.BR, BRANCHNAME, Count(*) AS Number
FROM EMPLOYEE, BRANCH
WHERE (EMPLOYEE.BR = BRANCH.BR)
GROUP BY EMPLOYEE.BR, BRANCHNAME;

The result is:

BR  BRANCHNAME      Number
01  Br01        6
02  Br02        4
03  Br03        5
04  Br04        6

I want to have the following result:

BR  BRANCHNAME      Number
01  Br01        6
02  Br02        4
03  Br03        5
04  Br04        6
05  Br05        0

Answer 1

It would seem you want a LEFT JOIN which gives a countable row with a null result even if there is no matching employee.

Since you've not added your table structure, I assume branchname is a field in the branch table.

SELECT branch.br, branch.branchname, COUNT(employee.br) AS Number
FROM branch
LEFT JOIN employee
  ON branch.br = employee.br
GROUP BY branch.br, branch.branchname

An SQLfiddle to test with (based on SQL Server since Access is not available)