MySQL Forums
Forum List  »  Full-Text Search

Re: Full text in search with 3 tables and inner join
Posted by: N. K.
Date: August 31, 2007 11:25AM

No one has responded to your question so I'll take a stab:

First off, you shouldn't need the wildcard AND statements. If you simply leave them out, the same rows will be returned.

As for adding the score bit, I think you're best off using a subselect for your select module_courseitems where you use the match and then join with the rest of the information and limit.



It would look something like this:
---------------------------------------
SELECT

module_courseitems.*,

module_courseschedule.*,

system_regions.*

FROM

(SELECT

course_id, course_code, course_name, course_descript, course_detail,

MATCH (course_name, course_descript, course_detail)

AGAINST ('$string')

AS score

FROM

module_courseitems

WHERE MATCH (course_name, course_descript, course_detail)

AGAINST ('$string' IN BOOLEAN MODE) LIMIT 0, 3) AS module_courseitems)

INNER JOIN

module_courseschedule

ON (module_courseschedule.course_id=module_courseitems.course_id)

INNER JOIN

system_regions

ON (module_courseschedule.schedule_region=system_regions.category_id)

WHERE

system_regions.nleft >= '1'

AND system_regions.nright <= '336'
------------------------------------

Hope that helps.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Full text in search with 3 tables and inner join
4498
August 31, 2007 11:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.