Re: Full text in search with 3 tables and inner join

[N. K.]

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.