Using concat in clause MATCH AGAINST - mysql

Question

I really hope that someone can help me with my problem. I would like to realize a match against query with group_concat.

I need something like this:

Select c.id, p.place 
  from content c 
  join place p on p.object_id = c.id 
 where match(group_concat(p.place)) AGAINST('"string1" "string2" "string3"', IN BOOLEAN MODE)  
   and not match (group_concat(p.place)) AGAINST('string4', IN BOOLEAN MODE)

I tried to use also having clause but doesn't work.

In the moment i have no idea how i can solve this. Could someone help me whit this problem? Thanks a lot.

Answer 1

You won't be able to use GROUP_CONCAT in a WHERE clause - aggregate functions can only be used in the HAVING clause. But you could use a derived table/inline view to get that GROUP_CONCAT output, like this:

SELECT c.id, 
       x.place 
  FROM CONTENT c 
  JOIN (SELECT p.object_id,
               p.place,
               GROUP_CONCAT(p.place) AS grp_place
          FROM PLACE p
      GROUP BY p.object_id) x ON x.object_id = c.id
 WHERE MATCH(x.grp_place) AGAINST('"string1" "string2" "string3"', IN BOOLEAN MODE)  
   AND NOT MATCH(x.grp_place AGAINST('string4', IN BOOLEAN MODE)

MySQL allows "hidden" columns in the GROUP BY: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html