Using MYSQL GROUP_CONCAT in the 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 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?

Trend

Answer 1

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

Siam

Fantastic! Thanks for being so quick and helpful. Didn't think it would be that easy! For some reason I hadn't come across HAVING. This now works- 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 HAVING subscriptionYear NOT LIKE '%2009%' ORDER BY searchSurname ASC – Trend

Isn't that redundant? If your WHERE is removing 2009 lines before the grouping, no grouping should even have 2009 in it? Or is it just a case of your example being just an example and not the actual query. – Siam

Answer 2

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

You could 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 is not possible to mention a GROUP_CONCAT or a alias on WHERE clause, because WHERE clause happens before it. The HAVING clause also fix this issue.

Azucenaazure

HAVING has to come after GROUP BY, not before it. otherwise, it throws a syntax error. see MySQL query reference for more details. – Josefina

You're right! I will edit my comment due to it! Thanks for the documentation! – Azucenaazure