CODEWITHSUNDEEP
mysql
acensored
acensored

Reputation: 87

MySQL Differences with counts caused by joins

i have a problem in MySQL where I use the COUNT function for conditions. However, when combining this with joins, although I use grouping, the COUNT values include ALL rows, even the ones filtered out. I'm providing a minimal working example, which however maybe does not make a practical sense or is designed smartly. So assume I have 3 tables:

So basically I have products and teams with ids and names. Products can be active (lets say that means that they are still in production or so). And then I have a relation which team is working on which product. To explain my problem, assume the following minimal amount of data to clarify the problem is contained inside the tables:

Now the query that I want to do is, in plain english: "I want all teams that are working on exactly 2 products while atleast one product must be active." The query in general works and is the following in mysql:

SELECT
teams.*,
"r_count:",
r_count.*,
COUNT(r_count.productId),
"r_active:",
r_active.*,
"p_active:",
p_active.*
FROM teams

INNER JOIN rel_production r_active ON r_active.teamId = teams.teamId
INNER JOIN products p_active ON p_active.productId = r_active.productId AND p_active.active

INNER JOIN rel_production r_count ON r_count.teamId = teams.teamId

GROUP BY teams.teamId, r_active.teamId

HAVING COUNT(r_count.productId) = 2 #4 is the problem!!!!!!!!!!!!!

Now them problem is with team 1. Because it is working on 2 active products, COUNT(r_count.productId) will be 4 and not 2. So my query will filter it out. Here is the screenshot with the result without the HAVING clause: enter image description here I see why this happens, because the two inner joins on rel_production will cause 4 rows to be generated. But then they are merged always together to one using the GROUP BY. So what I need is the COUNT after the GROUP and not before. How can I fix this?

Upvotes: 0

Views: 39

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522501

Perform the filtering on teams in a separate subquery, and then join to that:

SELECT
    t1.teamId,
    t1.name
FROM teams t1
INNER JOIN
(
    SELECT t1.teamId
    FROM rel_production t1
    INNER JOIN products t2
        ON t1.productId = t2.productId
    GROUP BY t1.teamId
    HAVING COUNT(DISTINCT t1.productId) = 2 AND SUM(t2.active) > 0
) t2
    ON t1.teamId = t2.teamId;

SQLFiddle

Upvotes: 2

Related Questions