Using MYSQL GROUP_CONCAT in a WHERE clause

Question

Is it possible to put GROUP_CONCAT in a MYSQL WHERE clause?

I have two tables (one for members and one for billing information). for example

Table of participants

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 extract information from both tables (I 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 “2009” from the column results subscriptionYear. Can't see if there is GROUP_CONCAT 2009 in the resulting file?

Answer 1

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