Count invitation response against events and response codes

Question

I have this table for Response Codes:

And this table for invitations:

My query so far gives this:

While I want to achieve this:

MY QUERY:

SELECT 
      i.eventId
     ,code.responseCode
     ,COUNT(i.attendeeResponse) responseCount
FROM invitations i
LEFT JOIN response_codes code
    ON code.responseCode = i.attendeeResponse
GROUP BY i.eventId, code.responseCode, i.attendeeResponse;

SQLFiddle

Answer 1

You need to construct a cartesian product of all eventIds and responseCodes at first (you can achieve it with join without condition):

select c.eventId
     , c.responseCode
     , count( i.attendeeResponse ) as responseCount
from ( select distinct t1.responseCode
            , t2.eventId
       from `response_codes` t1
       join `invitations` t2 ) c
left join `invitations` i on c.responseCode = i.attendeeResponse and c.eventId = i.eventId
group by c.eventId, c.responseCode;

SQLFiddle

Answer 2

You need to cross join the responsecode table to get the all combinations of eventid and responsecode.

SQL Fiddle

SELECT distinct
      i.eventId
     ,code.responseCode
     ,case when t.responseCount is null then 0 
      else t.responsecount end rcount
FROM invitations i
cross JOIN response_codes code
left join 
(SELECT i.eventId
       ,code.responseCode
       ,COUNT(i.attendeeResponse) responseCount
 FROM invitations i
 JOIN response_codes code
 ON code.responseCode = i.attendeeResponse
 group by i.eventid, code.responsecode) t
on t.responsecode =code.responsecode and t.eventid = i.eventid
order by i.eventid, code.responsecode desc