COUNT rows in 3 tables including zero values with MySQL

Question

I have a MySQL 5.6 database with 3 tables:

job_offer

+----+----------+-------------+-----------+
| id |   name   | position_id | status_id |
+----+----------+-------------+-----------+
|  1 | John     |           1 |         4 |
|  2 | Smith    |           1 |         4 |
|  3 | Williams |           2 |         2 |
+----+----------+-------------+-----------+

position

+----+----------+
| id |   name   |
+----+----------+
|  1 | frontend |
|  2 | backend  |
+----+----------+

status

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | contacted |
|  2 | declined  |
|  3 | rejected  |
|  4 | interview |
+----+-----------+

I would like to build a query that can count all job offers by their position and statuses. I have this query that performs almost the way I want it:

SELECT 
    position.name AS position_name,
    status.name AS status_name
    COUNT(job_offer.id) AS offers
FROM
    job_offer
LEFT OUTER JOIN
    position
        ON job_offer.position_id = position.id
LEFT OUTER JOIN
    status
        ON job_offer.status_id = status.id
GROUP BY
    position_name, status_name

Which gives me this result:

+---------------+-------------+--------+
| position_name | status_name | offers |
+---------------+-------------+--------+
| frontend      | interview   |      2 |
| backend       | declined    |      1 |
+---------------+-------------+--------+

The only problem is that I also need to display all existing statuses related to positions regardless of being NULL. So ideally it should look like this:

+---------------+-------------+--------+
| position_name | status_name | offers |
+---------------+-------------+--------+
| frontend      | contacted   |      0 |
| frontend      | declined    |      0 |
| frontend      | rejected    |      0 |
| frontend      | interview   |      2 |
| backend       | contacted   |      0 |
| backend       | declined    |      1 |
| backend       | rejected    |      0 |
| backend       | interview   |      0 |
+---------------+-------------+--------+

Is it possible to achieve this with one query? Thanks in advance for any help.

Answer 1

We can use a cross join approach between the position and status table to generate all possible combinations. Then, left join to job_offer and aggregate by position and status to find the counts:

SELECT
    p.name AS position_name,
    s.name AS status_name,
    COUNT(jo.id) AS offers
FROM position p
CROSS JOIN status s
LEFT JOIN job_offer jo
    ON jo.position_id = p.id AND
       jo.status_id = s.id
GROUP BY
    p.name,
    s.name
ORDER BY
    p.name,
    s.name;

Demo