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 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
s.display_name fields but i want the result with
Banner. But i get this result
c_link_type blank which is wrong, and that is because of FULLTEXT, Can anyone guide me to the right direction?
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.