MySQL sub select ignores condition-CodePudding

This query works good but ignores the AND members.active='1' condition completely.

It delivers always the same results, no matter if active is 1 or 0.

Any idea?

select amount, count(*) AS quantity
from
(
SELECT participations.member_number, COUNT(participations.member_number) AS amount
FROM participations
LEFT JOIN members
ON
participations.member_number=members.member_number
AND members.active='1'
GROUP BY participations.member_number
)
DT
group by amount
order by amount

CodePudding user response：

This is an other way to do it :

select amount, count(*) AS quantity
from
(
    SELECT participations.member_number, COUNT(participations.member_number) AS amount
    FROM participations
    LEFT JOIN members on participations.member_number=members.member_number 
    WHERE members.active='1'
    GROUP BY participations.member_number
)
DT
group by amount
order by amount;

Explanation :

with WHERE : After joining. Records will be filtered after join has taken place.

with ON : Before joining. Records (from right table) will be filtered before joining.

With an INNER JOIN, the clauses are equivalents.

CodePudding user response：

The issue might be with the AND clause in the LEFT JOIN statement. You can try moving the AND clause to the WHERE clause instead, like this:

FROM participations
LEFT JOIN members ON participations.member_number=members.member_number
WHERE members.active='1'
GROUP BY participations.member_number, members.active
) DT
GROUP BY amount
ORDER BY amount

This way, the AND clause will apply to the filtered data from participations and members tables, rather than to the whole data.