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?
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.
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.
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.
WHERE happens BEFORE the grouping, you want to use HAVING, which happens after the grouping.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With