Re: Full text search in mulitple-table query

[cristopher pierson ewing]

Shawn,
Thanks for the reply.
Here's the output of "SHOW CREATE TABLE" for one of the tables in 
question:

CREATE TABLE `tblcourseextrainfo` (
  `course_id` varchar(6) NOT NULL default '',
  `course_description` text,
  `course_intended_audience` text,
  `course_keywords` text,
  PRIMARY KEY  (`course_id`),
  FULLTEXT KEY `keywords` (`course_keywords`),
  FULLTEXT KEY `course_description` (`course_description`),
  FULLTEXT KEY `course_intended_audience` (`course_intended_audience`)
) TYPE=MyISAM
As you can see, I've created individual fulltext indeces for three fields 
in this table, there is another table called 'tblCourses' where I have a 
field called 'course_title' that also has a fulltext index.  The query in 
question pulls information from these two tables and about 4 others.  The 
result is a list of courses with all the information our customers need to 
see.

here's a sample of what the sql from one query might look like:
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,
  t6.course_description
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_type_code='MJ'
AND
  t1.course_webready='1'
AND
  t3.primary_location='1'
AND
  t2.course_start_date>'2005-01-01'
AND
  t2.course_end_date<'2005-12-31'
AND
  MATCH (t6.course_keywords,t1.course_title)
  AGAINST ('kidney,rheumatic');
Can you see any problems here that I'm missing?
Thanks,
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]
*******************************
On Thu, 27 Jan 2005 [EMAIL PROTECTED] wrote:
cristopher pierson ewing <[EMAIL PROTECTED]> wrote on 01/27/2005
04:01:22 PM:
I'm running a query that pulls information from about six different
tables
in a DB.  I'd like to be able to do a fulltext search on fields in
several
different tables.  The end result should be that any row with a fulltext

match in any of the fields in any table gets returned.  I've tried a
syntax that looks like this:
WHERE MATCH (table1.field1,table2.field2 table2.field3)
AGAINST ('some,nifty,words')
but I get back an error message that says:
ERROR 1210: Wrong arguments to MATCH
If all the ffields are from one table, then I get an error that says:
ERROR 1191: Can't find FULLTEXT index matching the column list
Is it possible to do a fulltext search on multiple fields in a quesry
that
references more than one table?  What would be the correct syntax for
such
a query?  Am I limited to doing this via a UNION-type query?
Thanks for any information that you can give me, and sorry if it seems a

trivial question, I can't seem to find an answer in the documentation
********************************
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]
*******************************

I don't think you can define a single full-text index that spans multiple
tables. That would require the capacity to FT index a view.  So I must
assume that you have created a FT index on one or more columns on each of
table1 and table2. If not, that may be your problem (you need to create a
FT index before you can use it).
It may be possible to say
SELECT...
FROM  table1
INNER JOIN table2
       ON ...
WHERE MATCH (table1.field1) AGAINST (...)
       OR MATCH (table2.field2, table2.field3) AGAINST (...)
and get the results you want. I can't test it because I don't have any FT
indexes, yet.
Can you describe your FT index structure?  ("SHOW CREATE TABLE xxxx\G"
creates great output for this purpose. Just edit out the fields that
aren't important to this problem if you are worried about size/secrets.)
That would go a long way to help us understand your problems.
Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]