Geek Answers Handbook

Mysql group_concat where

I have a problem with the following query (if this is a duplicate question, then I am very sorry, but I still can not find anything that could 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 cannot handle comparing GROUP_CONCAT columns in a WHERE clause. So my question is very simple. Is there a workaround for this, such as using a subquery or something like that? I really need this piece of code to work, and there is no alternative to use but to handle this in the request itself.

EDIT 1:

I will not show the actual code, as this may be confidential, I will need to check my peers. Anyway, I just wrote this code to give you an idea of ​​what the expression looks like, although I agree with you that it doesn't make much sense. I am going to check the answers below in a minute, then I will get back to you. Thnx again for all the help already!

EDIT 2:

Tried to use HAVING, but it only works when I do not use GROUP BY. When I try to do this, it gives me a syntax error, but when I delete GROUP BY, the query works fine. The fact is that I need GROUP BY, otherwise the request would be meaningless for me.

EDIT 3:

, HAVING GROUP BY, , , . , !

+5
3

HAVING WHERE.

... HAVING members LIKE '%peter%'

WHERE GROUP_CONCAT; HAVING .

: . , - Peter, .

, HAVING , ...

+12

Try

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

. , - .

+2

GROUP_CONCATis an aggregate function. You have to GROUP BY something. If you just need all the lines with %peter%in them, try

SELECT d.*, g.name  
FROM table_d AS d 
  LEFT OUTER JOIN table_g AS g 
  ON (d.eventid = g.id) 
WHERE g.name LIKE '%peter%';
0
source

More articles:


All Articles