Thanks Paul,
I figured that was the answer. Do I need to perform a second and third
query from my related tables with a join back to the 'main' table?
Example:
select main.id, main.description, maincat.name, subcat.name from main LEFT
JOIN maincat ON main.maincatid=maincat.id LEFT JOIN subcat ON
main.subcatid=subcat.id WHERE MATCH (name) AGAINST ('keywords') WHERE MATCH
(description, etc, etc2, etc3 ) AGAINST ('keywords')
select main.id, main.description, maincat.name, subcat.name from maincat
LEFT JOIN main ON maincat.id=main.maincat LEFT JOIN subcat ON subcat.id
=main.subcatid WHERE MATCH (name) AGAINST ('keywords')
select main.id, main.description, maincat.name, subcat.name from subcat LEFT
JOIN main ON subcat.id=main.subcat LEFT JOIN maincat ON maincat.id
=main.maincatid WHERE MATCH (name) AGAINST ('keywords')
I would then have 3 arrays that I could join and sort and use as my results.
I am not sure if this is more work than it is worth. I don't want to
duplicate data but I am thinking of just adding a 'maincatname' and
'subcatname' columns to my 'main' table and update them from the related
tables.
Thanks for your help,
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
