I have 3 tables which are part of my database.
debates (id 'PK', unit_id, starter_pack_id 'FK', title)
debate_stakeholders (id 'PK', starter_pack_id 'FK', name)
debate_groups (id 'PK', debate_id 'FK', student_id, stakeholder_id 'FK')
debates.id, debates.title, debate_stakeholders.name, and the Count of how many of those stakeholders occur within that particular debate
d.id AS debateId,
IF(COUNT(b.stakeholder_id) = 0, 0, COUNT(b.stakeholder_id)) AS total_freq
debate_stakeholders a LEFT JOIN debate_groups b ON b.stakeholder_id = a.id
LEFT JOIN debates as d ON b.debate_id = d.id
COUNT(*) < 3
ORDER BY a.id,d.id
I must admit that the table names confuse me. A debate__stakeholder is not related to a debate. It's rather a stakeholder belonging to a starter pack and there are also debates belonging to a starter pack. At least this is what I read from the table structures. Then a debate_group consists of a single student plus a stakeholder in a debate. It is strange to call this a group.
However, it seems you want to combine all stakeholders with all debates in a starter pack (i.e. get all combinations). Then you want to count how many students are related to each such debate / stakeholder combination. So write a query to count students per debate and stakeholder (an aggregation query grouped by debate and stakeholder) and use this as a subquery you outer-join to the debate / stakeholder combinations.
SELECT d.id, d.title, ds.name, COALESCE(dg.students, 0) AS "count" FROM debates d JOIN debate_stakeholders ds ON ds.starter_pack_id = d.starter_pack_id LEFT JOIN ( SELECT debate_id, stakeholder_id, COUNT(*) AS students FROM debate_groups GROUP BY debate_id, stakeholder_id ) dg ON dg.debate_id = d.id AND dg.stakeholder_id = ds.id;