How to put a filter condition in group_concat columns?

Question

TABLE 1

+----+--------+
| id | name   |
+----+--------+
|  2 | robin  |
|  3 | jyothi |
|  1 | angel  |
+----+--------+

TABLE 2

+----+---------+--------+
| id | hobbies | ref_id |
+----+---------+--------+
|  1 | walking |      1 |
|  2 | chating |      1 |
|  3 | reading |      2 |
|  4 | walking |      2 |
+----+---------+--------+

I want the name and their hobbies that have a hobby, "walking" or the name "robin"

+----+-----------------+
| name | hobbies       | 
+----+-----------------+
|  1 | walking,chating |      
|  3 | reading,surfing |    
+----+-----------------+

query used:

select name, group_concat(hobbies) as all_hobbies
from test1,test2
where test1.id = test2.ref_id 
and test1.id in (select ref_id
              from test2
              where hobbies = 'walking')
group by name

I do not want to use a subquery or save the whole result as a virtual table and search on all_hobbies, although there is a where clause. I want to execute group_concat columns through where where or in some way, since its increasing my SQL_JOIN_SZIE

I cannot use it here, as this should act as a condition or, for example:

Any hobby is walking, or the name robin.

Answer 1

mysql, , , , . HAVING WHERE GROUP BY.

select name, group_concat(hobbies) as all_hobbies
from test1,test2
where test1.id = test2.ref_id and test1.name = 'robin'
group by name
having all_hobbies like '%walking%'

Answer 2

:

having all_hobbies like '%walking%' or
name like '%robin%'

caluse, concat colums

Answer 3

I would join two tables. Then id selects the concatenation using the field field (field a) b and put my restriction in the Where clause.