MYSQL Left-Join Table Selecting Data Issue - Logic Error

Question

I have the following 4 tables.

1. DEBATES. 
   [ID, STARTER_PACK_ID]
2. DEBATE_GROUPS.
   [ID, DEBATE_ID, STAKEHOLDER_ID]
3. DEBATE_STAKEHOLDERS.
   [ID, STARTER_PACK_ID, NAME]
4. DEBATE_STARTER_PACKS.
   [ID]

I am trying to select every debate group, with every stakeholder id, name of stakeholder id and the count of that stakeholder to that debate WHERE the starter_pack_id equals a certain values. For example, These have to be shown, even if the count is 0 or null values are present

The expected outcome of this is as follows..

DEBATE_ID | STAKEHOLDER_ID | COUNT(Example)
-------------------------------------------
   1             1               2
   1             2               1
   1             3               3
   2             1               4
   2             2               1
   2             3               2

The issue is that it isn't showing the debates which haven't been used in the database, which is what I required. What should I change in the code below to retrieve this information?

Below is my attempt...

SELECT 
    a.id, 
    a.name, 
    a.abbreviation, 
    b.debate_id, 
    IF(COUNT(b.stakeholder_id) = 0, 0, COUNT(b.stakeholder_id)) AS total_freq, 
    COUNT(b.stakeholder_id) AS freq 
FROM 
    debate_stakeholders a LEFT JOIN debate_groups b ON b.stakeholder_id = a.id 
GROUP BY
    a.id, b.debate_id 
HAVING 
    COUNT(*) < 3 
ORDER BY 
    a.id,b.debate_id,b.stakeholder_id

mickmackusa · Accepted Answer · 2025-11-22 15:18:53Z

1

SELECT 
    a.id, 
    a.name, 
    a.abbreviation, 
    d.id, 
    IF(COUNT(b.stakeholder_id) = 0, 0, COUNT(b.stakeholder_id)) AS total_freq, 
    COUNT(b.stakeholder_id) AS freq 
FROM 
    debate_stakeholders a
LEFT JOIN
    debate_groups b ON b.stakeholder_id = a.id 
RIGHT JOIN
    debates as d ON b.debate_id = d.id
GROUP BY
    a.id, b.debate_id 
HAVING 
    COUNT(*) < 3 
ORDER BY 
    a.id,b.debate_id,b.stakeholder_id

edited Nov 22 at 15:18

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Aug 9, 2016 at 7:12

Dmitriy Korobkov

8971 gold badge11 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

MYSQL Left-Join Table Selecting Data Issue - Logic Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related