Geek Answers Handbook

Using concat in MATCH AGAINST section - mysql

I really hope someone can help me with my problem. I would like to implement a match with a request using 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 the availability of the sentence, but it does not work.

At that moment when I have no idea how I can solve this. Can someone help me deal with this issue? Many thanks.

+4
source share
1 answer

You cannot use GROUP_CONCAT in the WHERE clause — aggregate functions can only be used in the HAVING . But you can use the view / inline view to get this GROUP_CONCAT output, for example:

 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 GROUP BY: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

+3
source

More articles:


All Articles