Re: Full text search in mulitple-table query

Fri, 28 Jan 2005 08:50:42 -0800 

On Fri, 28 Jan 2005, Santino wrote:

At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote: 
Shawn,

Okay, it turns out that I can solve my problem by reordering the elements of the WHERE clause at the end of the query I sent before. I've gotten good results with the following version (it breaks all the fields in the Fulltext search into separate searches):

SELECT
  t1.course_id,
  t1.course_title,
  t1.course_subtitle,
  t1.course_brochure_path,
  t2.course_start_date,
  t2.course_end_date,
  t4.location_name1,
  t4.location_name2,
  t4.location_city,
  t4.location_state,
  t5.allow_online_registration,
  t6.course_keywords
FROM
  cme_course_info.tblCourses t1
  LEFT JOIN cme_course_info.tblCourseDates t2
    ON t1.course_id = t2.course_id
  LEFT JOIN cme_course_info.tblCourseLocations t3
    ON t1.course_id = t3.course_id
  LEFT JOIN cme_course_info.tblLocations t4
    ON t3.location_record = t4.location_record
  LEFT JOIN cme_course_info.tblCourseWebSwitches t5
    ON t1.course_id = t5.course_id
  LEFT JOIN cme_course_info.tblCourseExtraInfo t6
    ON t1.course_id = t6.course_id
WHERE
  t1.course_webready='1'
AND
  t3.primary_location='1'
AND
  MATCH (t6.course_keywords) AGAINST ('care')
OR
  MATCH (t6.course_description) AGAINST ('care')
OR
  MATCH (t6.course_intended_audience) AGAINST ('care')

Create a fulltext index on 3 columns and search :
MATCH (t6.course_keywords, t6.course_description, t6.course_intended_audience) AGAINST ('care')
So, does seearching on multiple columns only work if you create the fulltext index on all of them at the same time? When I read the docs they seemed to imply that indeces created on multiple columns wouldn't be individually searchable. In other words, if I create a fulltext index on col1, col2, and col3, then I will not be able to match against only col1. I created the three as separate indeces so that I could maintain the ability to search each separately at some point. Am I wrong to do so?


OR
  MATCH (t1.course_title) AGAINST ('care')
AND
  t2.course_start_date>'2005-02-01'
AND
  t2.course_end_date<'2005-12-31'
AND
  t1.course_type_code='MJ'
ORDER BY
  t2.course_start_date, t2.course_end_date, t1.course_title;

This pretty much ends my problem, except for one interesting aside that still has me confused. If I just slightly alter the order of all the various sub-clauses in the WHERE portion of the query, I get some courses that violate the requirement "course_type_code='MJ'" (last part of WHERE)

Specifically, if I take the MATCH parts and move them up to right after the WHERE, like so:

WHERE
  MATCH (t6.course_keywords) AGAINST ('care')
OR
  MATCH (t6.course_description) AGAINST ('care')
OR
  MATCH (t6.course_intended_audience) AGAINST ('care')
OR
  MATCH (t1.course_title) AGAINST ('care')
AND
  t2.course_start_date>'2005-02-01'
AND
  t2.course_end_date<'2005-12-31'
AND
  t1.course_webready='1'
AND
  t3.primary_location='1'
AND
  t1.course_type_code='MJ'

I think you must use ():

Yep, that fixed the problem, now I can put the ORed portion of the filter at the front, where it logically seems to belong and all works just fine. Thanks for the assist!


WHERE (
 MATCH (t6.course_keywords) AGAINST ('care')
OR
 MATCH (t6.course_description) AGAINST ('care')
OR
 MATCH (t6.course_intended_audience) AGAINST ('care')
OR
 MATCH (t1.course_title) AGAINST ('care')
)
AND
 t2.course_start_date>'2005-02-01'
AND
 t2.course_end_date<'2005-12-31'
AND
 t1.course_webready='1'
AND
 t3.primary_location='1'
AND
 t1.course_type_code='MJ'

Suddenly, I get courses showing up that violate all the later requirements, such as the ones on course_start_date, course_end_date, and so on to the end.

Is there a requirement as to which order sub-clauses of a WHERE clause have to follow? I couldn't find anything that described this, but I'm perfectly willing to admit I have a hard time finding lots of things in the online docs.

Thanks for any lucidity anyone can lend,

Cris

********************************
Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
*******************************


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to