Well after some pondering I found a pretty easy work around: SELECT main.id, description, maincat.name, subcat.name from main LEFT JOIN maincat ON maincat.id=main.maincatid LEFT JOIN subcat ON subcat.id=main.subcatid WHERE MATCH (description) AGAINST ('keywords') OR WHERE MATCH (maincat.name) AGAINST ('keywords') OR WHERE MATCH (subcat.name) AGAINST ('keywords')
You can even add the MATCHes to your select to get a total relavence: (MATCH (description) AGAINST ('keywords') + MATCH (maincat.name) AGAINST ('keywords') + MATCH (subcat.name) AGAINST ('keywords')) as relavence which you can also sort by. I hope this helps somebody. Dan > At 13:43 -0700 3/26/02, Dan Tappin wrote: >> This is a follow-up to a MySQL keyword text search question I had answer a >> few days ago. >> >> I have table 'main' which has two INT columns 'maincat' and 'subcat' which >> hold an index number from to other tables maincat and subcat. These table >> each hold descriptive names ('name') for each of the main categories and sub >> categories. >> >> I have no problem using MATCH to search the various fields of 'main' using >> an INDEX. The problem is I want to create a search across the related >> 'name' fields of the other two tables. >> >> Here is what I have so far: >> >> select main.id, description, maincat.name, subcat.name from main LEFT JOIN >> clients ON forsale.clientid=clients.id WHERE MATCH (description) AGAINST >> ('keywords') >> >> This works but I can only MATCH in the columns of 'main'. >> >> Now my first thought was to try this: >> >> select main.id, description, maincat.name, subcat.name from main LEFT JOIN >> clients ON forsale.clientid=clients.id WHERE MATCH (description, >> maincat.name, subcat.name) AGAINST ('keywords') >> >> Which results in an error. >> >> Can anyone tell me if I can do this using MATCH? > > Nope. > > The columns named in the MATCH() must match the columns for a FULLTEXT > index, and indexes don't cross tables. > >> >> Thanks, >> >> Dan --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php