Question 20998 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL sub select ignores condition

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search