Nnaut Nnaut - 1 month ago 9x
MySQL Question

MySQL LEFT JOIN Count column Join 3 tables

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')

For this purpose all debates share the same stakeholders (4 stakeholders in total, all of these stakeholders are referenced for all debates).

The aim of my expected outcome to query all the debates, that shows the
debates.id, debates.title, debate_stakeholders.name, and the Count of how many of those stakeholders occur within that particular debate
, in relation to the relative stakeholder irrespective if the count of stakeholders is 0. This part is important as when I perform additional queries, I need to know which query counts are greater than or equal to one, zero and null.

Here is the sample data of my database:

enter image description here

My expected outcome: (The count is just to show what It could look like)

enter image description here

I have attempted to create this MySQL query, but I am unable to achieve my exact requirements.

I have tried queries such as
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
a.id, b.debate_id,d.id

COUNT(*) < 3
ORDER BY a.id,d.id

But that hasen't quite planned out.


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,
       COALESCE(dg.students, 0) AS "count"
FROM debates d
JOIN debate_stakeholders ds
    ON ds.starter_pack_id = d.starter_pack_id
    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;

Demo here: