[ACCEPTED]-Using MYSQL GROUP_CONCAT in the WHERE clause-aggregate
Accepted answer
WHERE happens BEFORE the grouping, you want 1 to use HAVING, which happens after the grouping.
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.
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.