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

>Solution :

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.