mysql group_concat in where

Question

I am having a problem with the following query(if this is a duplicate question then i'm terribly sorry, but i can't seem to find anything yet that can help me):

SELECT d.*, GROUP_CONCAT(g.name ORDER BY g.name SEPARATOR ", ") AS members
FROM table_d AS d LEFT OUTER JOIN table_g AS g ON (d.eventid = g.id)
WHERE members LIKE '%p%';

MySQL apparently can't handle a comparison of GROUP_CONCAT columns in a WHERE clause. So my question is very simple. Is there a workaround for this, like using sub-query's or something similar? I really need this piece of code to work and there is not really any alternative to use other than handling this in the query itself.

EDIT 1:

I won't show the actual code as this might be confidential, I'll have to check with my peers. Anyway, I just wrote this code to give you an impression of how the statement looks like although I agree with you that it doesn't make a lot of sense. I'm going to check the answers below in a minute, i'll get back to you then. Again thnx for all the help already!

EDIT 2:

Tried using HAVING, but that only works when i'm not using GROUP BY. When I try it, it gives me a syntax error, but when I remove the GROUP BY the query works perfectly. The thing is, i need the GROUP BY otherwise the query would be meaningless to me.

EDIT 3:

Ok, so I made a stupid mistake and put HAVING before GROUP BY, which obviously doesn't work. Thanks for all the help, it works now!

Answer 1

Use HAVING instead of WHERE.

... HAVING members LIKE '%peter%'

WHERE applies the filter before the GROUP_CONCAT is evaluated; HAVING applies it later.

Edit: I find your query a bit confusing. It looks like it's going to get only one row with all of your names in a single string -- unless there's nobody in your database named Peter, it which case the query will return nothing.

Perhaps HAVING isn't really what you need here...

Answer 2

Try

SELECT ...
...
WHERE g.name = 'peter'

instead. Since you're just doing a simple name lookup, there's no need to search the derived field - just match on the underlying original field.