MySQL complex count on cross join

Question

Thank you for reading, I've tried this for four hours now with some good progress but I just can't get my head around it.

I've simplified the application to just the parameters I'm struggling with.

I have a database of customers that have used services, one for each year and two lookup tables to allow for coding of the results.

Table - Gender
gender_code | gender_name
          1 | Male
          2 | Female
          3 | Other

Table - Service
service_code | service_name
           1 | WiFi
           2 | Network
           3 | Telephone

Table - Customers13
transactionid | customerid | service_code | gender_code
            1 |          4 |            3 |           1
            2 |          7 |            1 |           2
            3 |          9 |            1 |           1

Table - Customers14
transactionid | customerid | service_code | gender_code
            1 |         13 |            2 |           2
            2 |          4 |            2 |           1
            3 |         17 |            2 |           2

What I want to get is a table returned which has all possible permutations of service and gender and a count of each for each year. I started off trying to get the count right and used the following MySQL query:

select t.service_name, t.gender_name, count13, count14
from (select service_name, gender_name, count(Customers13.gender_code) as count13
from Customers13  
inner join Gender on Customers13.gender_code = Gender.gender_code  
inner join Service on Customers13.service_code = Service.service_code 
group by service_name, gender_name) t
inner join (select service_name, gender_name, count(Customers14.gender_code) as count14
from Customers14  
inner join Gender on Customers14.gender_code = Gender.gender_code  
inner join Service on Customers14.service_code = Service.service_code 
group by service_name, gender_name) m on m.service_name=t.service_name and 
m.gender_name=t.gender_name

This gets me close returning the table I want but only where there is a match in both tables. What I want is to get a line even when there is a match in no tables, for example:

service_name | gender_name | count13 | count14
WiFi         | Male        |       1 |       0
WiFi         | Female      |       1 |       0
WiFi         | Other       |       0 |       0
Network      | Male        |       0 |       1
Network      | Female      |       0 |       2
Network      | Other       |       0 |       0
Telephone    | Male        |       1 |       0
Telephone    | Female      |       0 |       0
Telephone    | Other       |       0 |       0

My last attempt was to use a cross join to get the first two columns correct but I've no idea how to make the jump to populating the count fields from there:

select service_name, gender_name from Service cross join Gender

Answer 1

Bind all genders to all services with a cross join, then left join the union subquery:

SELECT
    s.service_name, g.gender_name, COUNT(c.cnt13), COUNT(c.cnt14)
FROM
    service s
CROSS JOIN
    gender g
LEFT JOIN
    (
        SELECT gender_code, service_code, 1 AS cnt13, NULL AS cnt14
        FROM Customers13
        UNION ALL
        SELECT gender_code, service_code, NULL, 1
        FROM Customers14
    ) AS c
    USING (gender_code, service_code)
GROUP BY s.service_name, g.gender_name