mysql - SQL query for retrieving the count of matching value pairs from a single table with different foreign keys? -

i have mind-bending problem mysql / mariadb query, table structure follows:

event

• id int(11)
• time datetime
• description varchar(1000)

report

• id int(11)
• event_fk int(11) refers event
• reporttemplate_fk int(11) refers reporttemplate

reporttemplate localized report templates. types: before event / after event, each language

• id int(11)
• type varchar(255)
• name varchar(255)
• template varchar(10000)

reportvalue

• report_fk int(11) refers report
• key varchar(255)
• value varchar(255)

there 2 kinds of reporttemplates, 1 before event (all events have this) , 1 after event (only events have this). there tens of different reportvalues before report, , subset of around dozen reportvalues after report.

the problem this: how can form query calculates, each event, count of matching key-value-pairs before- , after-reports in reportvalue-table, when reports of both types exist event?

something should it:

select     e.id     count(before.id),     count(after.id)     event e     join reporttemplate before on e.id = before.event_fk , before.type = 'before'     join reporttemplate after on e.id = after.event_fk , after.type = 'after' group     e.id