Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using MYSQL GROUP_CONCAT in the WHERE clause

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?

like image 413
iagdotme Avatar asked Jan 06 '10 15:01

iagdotme


People also ask

What is the use of Group_concat in MySQL?

The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.

What is the difference between concat and Group_concat in MySQL?

The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.

What is separator in MySQL?

The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function uses a comma (,) as the default separator. The GROUP_CONCAT function ignores NULL values.


1 Answers

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

like image 185
MindStalker Avatar answered Oct 23 '22 05:10

MindStalker