Using MYSQL GROUP_CONCAT in the WHERE clause
aggregate, mysql, sql
Solution
WHERE happens BEFORE the grouping, you want to use HAVING, which happens after the grouping.
Problem
Is it possible to put GROUP_CONCAT in a MYSQL WHERE clause? I have two tables (one for members and one for payment info). For example Members Table ``` num, memNumber, fullName, coporateName, surname 001, mem0010, Joe Bloggs, NULL, Bloggs 002, mem0015, NULL, BBC 003, mem0017, John Peters, NULL 004, mem0101, Emma Jane, NULL ``` Payment Table ``` num, memberID, subscriptionYear, amount 001, mem0010, 2008, 30 003, mem0010, 2010, 40 004, mem0015, 2010, 40 005, mem0017, 2009, 35 006, mem0101, 2009, 35 007, mem0017, 2010, 40 ``` I have the following query to retrieve info from both tables (I have simplified it to make it more readable). ``` 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 WHERE `subscriptionYear` NOT LIKE '%2009%' GROUP BY members.num ORDER BY `searchSurname` ASC ``` But it removes the "2009" from the results of the `subscriptionYear` column. Can't see if 2009 is in the resultant GROUP_CONCAT?