MySQL LEFT JOIN Count column Join 3 tables

Question

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:

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

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

I have tried queries such as SELECT a.id, a.name, a.abbreviation, d.id AS debateId, IF(COUNT(b.stakeholder_id) = 0, 0, COUNT(b.stakeholder_id)) AS total_freq FROM 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 GROUP BY a.id, b.debate_id,d.id HAVING COUNT(*) < 3 ORDER BY a.id,d.id LIMIT 1

But that hasen't quite planned out.

What have you tried so far? You should include this, and some sample data would be even better. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 10, 2016 at 5:55
So the debates table has two unique keys? id and also starter_pack_id? Why is this? — Thorsten Kettner
– Thorsten Kettner, Commented Aug 10, 2016 at 6:48
@ThorstenKettner Why are records being filtered off in a LEFT JOIN? Have a look here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 10, 2016 at 6:53
because debates.starter_pack_id is a foreign key from another table, but it is irrelevant for this solution. In fact I should not have included it. I will remove it — Nnaut
– Nnaut, Commented Aug 10, 2016 at 6:54

Tim Biegeleisen · Accepted Answer · 2016-08-10 08:20:46Z

2

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;

Demo here:

SQLFiddle

edited Aug 10, 2016 at 8:20

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Aug 10, 2016 at 8:06

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nnaut Over a year ago

You are champion. This seems to accomplish what I am trying to achieve. Thanks man

Thorsten Kettner Over a year ago

@Tim Biegeleisen: Thank you.

Nnaut Over a year ago

Could one addtion be made?. What if I want to use that exact, but to show only records where the count is less than '3' or instance?, but still show the other null or 0 count records

Thorsten Kettner Over a year ago

Add WHERE COALESCE(dg.students, 0) < 3 at the end.

d.coder · Accepted Answer · 2016-08-10 06:39:13Z

0

Try this, hope this will help:

SELECT d.id, COALESCE(d.title, 'NA') AS title, COALESCE(ds.name, 'NA') AS name, COUNT(ds.id) AS count
FROM debates d
LEFT JOIN debate_groups AS dg ON d.id = dg.debate_id
LEFT JOIN debate_stakeholders AS ds ON dg.stakeholder_id = ds.id
GROUP BY d.id, dg.stakeholder_id

edited Aug 10, 2016 at 6:39

answered Aug 10, 2016 at 6:22

d.coder

2,03818 silver badges24 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

Nope. You filtered off some of the moderators. The OP wants every moderator to appear for each debate.

Nnaut Over a year ago

I more so want every stakeholder, to appear for every debate, irrespective if there count is 0

Collectives™ on Stack Overflow

MySQL LEFT JOIN Count column Join 3 tables

2 Answers 2

SQLFiddle

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related