Re: [Newbie] MATCH and INDEX question (using a JOIN in a MATCH statement)
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
Re: [Newbie] MATCH and INDEX question (using a JOIN in a MATCH statement)
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
Re: [Newbie] MATCH and INDEX question (using a JOIN in a MATCH statement)
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
