How to put filter condition on 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 name & their hobbies who have hobby as 'walking' or name is 'robin'

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

used query:

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 don't want to use subquery or save the entire result as virtual table & the search on all_hobbies though where clause. I want to do accomplish the group_concat columns through where clause or some way around as its increasing my SQL_JOIN_SZIE

I cannt use having here as that has to act as or condition for eg:

Either hobbies is 'walking' or name is 'robin'

Answer 1

I don't have mysql installed on this machine, but try this query out, I think it should work. You can think of the HAVING clause as a WHERE clause for your 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

It can be accomplished by:

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

having caluse will work on group concat colums & normal coumns