[ACCEPTED]-Using MYSQL GROUP_CONCAT in the WHERE clause-aggregate

Accepted answer
Score: 27

WHERE happens BEFORE the grouping, you want 1 to use HAVING, which happens after the grouping.

Score: 0

The clause "HAVING" is used when 9 it is necessary to use a filter after a 8 group (GROUP BY, GROUP_COCANT, ...). Instead, the 7 "WHERE" clause creates a filter 6 before the agroupment happens.

You could 5 use the code bellow:

SELECT members.num, members.memNumber , members.fullName , members.corporateName ,
       CONCAT(members.corporateName , members.surname) AS searchSurname ,
       GROUP_CONCAT(payment.subscriptionYear) As subscriptionYear ,
       GROUP_CONCAT(payment.amount) AS amount    
FROM members 
LEFT JOIN payment ON members.memNumber = payment.memberID    
HAVING `subscriptionYear` NOT LIKE '%2009%'    
GROUP BY members.num    
ORDER BY `searchSurname` ASC

In case, on MYSQL it 4 is not possible to mention a GROUP_CONCAT 3 or a alias on WHERE clause, because WHERE 2 clause happens before it. The HAVING clause 1 also fix this issue.

More Related questions