views:

28

answers:

1

Hello I' trying to select from joined column:

SELECT 
   A.idAd,
   A.ads_in_Cat,
   A.title,
   A.currency,
   A.price,
   A.in_dpt,
   A.description,
   D.*, 
   S.* 
FROM ads A
LEFT JOIN dept D ON D.id_dept = A.in_dpt
LEFT JOIN sub_cat_ad AS  ON S.id_sub_cat = A.ads_in_Cat
WHERE MATCH(A.title, A.description) AGAINST(:searchTerm) 
   OR MATCH (S.sub_cat_name) AGAINST(:searchTerm) 

Could someone please tell me why MySQL returns : 1191 Can't find FULLTEXT index matching the column list in... unless I use IN BOOLEAN MODE?

PS the table S has fulltext defined for each colunm. MySQL ver 5.1.49

+1  A: 

Do you still get the error when you use:

   SELECT A.idAd,
          A.ads_in_Cat,
          A.title,
          A.currency,
          A.price,
          A.in_dpt,
          A.description,
          D.*, 
          s.* 
     FROM ads A
LEFT JOIN dept D ON D.id_dept = A.in_dpt
LEFT JOIN sub_cat_ad s ON s.id_sub_cat = A.ads_in_Cat
                      AND MATCH (s.sub_cat_name) AGAINST(:searchTerm) 
    WHERE MATCH(A.title, A.description) AGAINST(:searchTerm) 
OMG Ponies
Thanks OMG Ponies, yes im still getting the same error.
jartaud
@jartaud: What if you don't include the FTS on `sub_cat_ad` at all? It's help to know the search term you're testing with, too.
OMG Ponies
@OMG POnies Yes, cause the user can search by title, description or just entering the category name
jartaud
OMG Ponies
@OMG Ponies, yes everythnig works great if I dont include the FTS on sub_cat_ad. Donno, but it seems MySQL 5.1.49 ignores the defined fulltext indices.
jartaud