How to prevent query from removing 'null' records

Question

Here are 3 tables :

cities(id PK, name, zipcode)

jobs(id PK, title)

persons(id PK, first_name, last_name, job_id FK jobs, city_id FK cities)

Each person has a job and lives in a city, both cities and jobs can have at least 0 persons (0..n).

I would like to count persons for each job and city without removing cities and jobs which do not have persons :

> +----------+----------+---------------+
| city_id    | job_id   | count(p.id)   |
+------------+----------+---------------+
|        140 |        1 |             0 |
|        249 |       37 |             1 |
|        249 |       40 |             1 |
|        249 |      269 |             1 |
|        250 |     4823 |             3 |
|        251 |        1 |             4 |
|        251 |      205 |             1 |
|        433 |        1 |             0 |
|        433 |       40 |             1 |
|        433 |       23 |             1 |
|        433 |     1346 |             1 |
|        434 |        5 |             5 |
|        434 |       70 |             1 |
|        434 |     5332 |             1 |

I guess the query should look like so :

SELECT job_id, city_id, COUNT(p.id)
FROM persons p 
???? JOIN jobs j ON p.job_id=j.id 
???? JOIN cities c ON p.city_id=c.id 
GROUP BY j.id, c.id

I know it cannot be done using an INNER JOIN as all rows containing no joined references will be ignored.

I tried RIGHT JOIN but depending upon JOIN's order results are not the same.

Add some sample table data and the expected result - as formatted text, not images. — jarlh
Expected result is great, but we need to know the table data used to get that result as well. — jarlh
I cannot have this for privacy reasons (I have used a mock schema for my question). — Aleks Ben Maza

Sergey L Sergey L · Accepted Answer · 2017-10-23T21:27:26

You should start you query with cities then jobs and then count a number of persons. But in your database schema it is 'hard' to do efficiently as cities and jobs are not linked. The best way to handle it is to change you database schema like this:

cities(id PK, name, zipcode)

jobs(id PK, title, city_id FK cities)

persons(id PK, first_name, last_name, job_id FK jobs)

You can see the difference - a job is linked to the city, a person is linked to a job that in turn is linked to the city where this job is.

If you follow this way, you can write a query like this:

select c.id as city_id, j.id as job_id, count(p.id)
from cities c
inner join jobs j on (j.city_id = c.id)
left outer join persons p on p.job_id = j.id
group by c.id, j.id

But if you keep your existing schema, you need to have cross join between cities and jobs and this might be inefficient and produce noise results (jobs that are not on present in a certain city). You need this query:

select c.id as city_id, j.id as job_id, count(p.id)
from cities c
cross join jobs j 
left outer join persons p on (p.job_id = j.id) and (j.city_id = c.id)
group by c.id, j.id

How to prevent query from removing 'null' records

1 Answers