mysql join query

Question

I have a query with the following tables (reduced to show only the interested columns).

t1
code

t2
code, period, status

t3
period, desc

Now what I have is,

t3 is a table of unique "periods".

t1 is a table of unique codes.

t2 is the join table linking both together, along with a status, for sake of this example status=(A,B,C).

What I'm doing is create a query result that is grouped by "period" and has the count of 'codes' in each status.

That is easy to solve, but want I want to extend this to, is to have not only a count of 'code's in A, B and C but also a count of the codes that AREN'T associated with a period or in other words, a count of the codes that aren't in t2 for a given period.

So the result i'm looking for is

Period    A    B    C   (Codes from t1 not found in t2)
P1        10   5    2   3
P2        5    5    5   10

Answer 1

You can use cross join to select a matrix of all codes for all periods. That allows you to count the rows which are not present:

select
    sum(case when t2.status is 'A' then 1 else 0 end) as ACount,
    sum(case when t2.status is 'B' then 1 else 0 end) as BCount,
    ...
    sum(case when t2.code is null then 1 else 0 end) as NotPresentCount
from       t1
cross join t3
left join  t2 
on         t2.code = t1.code and t2.period = t3.period
group by   t3.period