CODEWITHSUNDEEP
mysqlsqlfull-text-search
jogesh_pi
jogesh_pi

Reputation: 9782

Full Text Search generates wrong result

I am using FULLTEXT search in two different tables in single query. Which generates wrong result.

select c.*, s.s_name, s.logo, s.s_slug, cm.coupon_code, cm.c_shorturl, cm.c_shorturl_id 
from ci_coupons c 
left join ci_stores s on s.store_id = c.store_id 
left join ci_coupons_mapper cm on cm.coupon_id = c.coupon_id 
where c.c_link_type like "%Banner%" 
      and c.c_width='300' 
      and match(c.c_name) against('+trend +micro') or match(s.display_name) against('+trend +micro')  

order by c.coupon_id desc

In the above query, i am searching +trend +micro in c.c_name and s.display_name fields but i want the result with c_link_type has Banner. But i get this result

enter image description here

That has c_link_type blank which is wrong, and that is because of FULLTEXT, Can anyone guide me to the right direction?

Upvotes: 0

Views: 38

Answers (1)

Unicorno Marley
Unicorno Marley

Reputation: 1884

The best guess I can see with the data provided in your screenshot, you are missing parens around:

and (match(c.c_name) against('+trend +micro') or match(s.display_name) against('+trend +micro'))

I bet your s.display_name column has 'Trend Micro' in it.

Upvotes: 2

Related Questions