sql - How to map rows of two tables to each other based on the relevance in MySQL? -

i have following tables in database: courses (whole data of sports classes), coursedata (with copies of courses.title , courses.description -- needed fulltext index / relevance search), sports (list of sports), , courses_sports (association table) -- see below.

now want map courses relevance based sports , fill courses_sports data automatically. needs 2 steps.

1. collect data apporiate select.

2. write data association table.

this post first step. have troubles writing query. i've tried:

select     courses.id,     sports.id     courses join     coursedata on coursedata.id = courses.coursedata_id join     sports on match (coursedata.title) against (sports.title) > 0 -- test -- sports on match (coursedata.title) against ('basketball') > 0 -- works. 

this query not working:

error code: 1210

incorrect arguments against

how implement mapping correctly?

---

additional information: relevant tables

courses

field               type             key     ------------------  ---------------  ------  id                  int(11)          pri              title               varchar(100)                      description         varchar(1000)                     coursedata_id       int(11)          uni      ... 

coursedata

field        type           collation        null    key      -----------  -------------  ---------------  ------  ------   id           int(11)        (null)           no      pri      title        varchar(100)   utf8_general_ci  yes     mul      description  varchar(1000)  utf8_general_ci  yes     mul       create table `coursedata` (   `id` int(11) not null auto_increment,   `title` varchar(100) default null,   `description` varchar(1000) default null,   primary key (`id`),   fulltext key `searchcoursetitle` (`title`),   fulltext key `searchcoursedescription` (`description`) ) engine=myisam auto_increment=5208 default charset=utf8 

sports

field     type                   collation        null    key      --------  ---------------------  ---------------  ------  ------   id        int(11)                (null)           no      pri      title     varchar(50)            utf8_general_ci  no               category  varchar(50)            utf8_general_ci  yes              type      enum('sport','dance')  utf8_general_ci  yes              

courses_sports

field      type     collation  null    key      ---------  -------  ---------  ------  ------   course_id  int(11)  (null)     no      pri      sport_id   int(11)  (null)     no      pri      

you forgot provide common field between sports , courses in case join. forgot statement before match.

join     sports on match (coursedata.title) against (sports.title) > 0 ,     sports on match (coursedata.description) against (sports.title) > 0 

so, should this:

join     sports on (course.commonid = sports.commonid) match  

and since want both coursedata.title , coursedata.description join them in match.

join     sports on (course.commonid = sports.commonid) match(coursedata.title, coursedata.description) 

and not use field in sports.title because mean sports title compare against traverse , put value inside against.

join     sports on (course.commonid = sports.commonid) match(coursedata.title, coursedata.description) against('martial') 

and use boolean mode because not work if have 50% or more against matches

join     sports on (course.commonid = sports.commonid) match(coursedata.title, coursedata.description) against('martial' in boolean mode) 

i have sql fiddle sample 2 tables, namely, coursedata , sports , added common field between two.

and don't have join sports table instead traverse on , match against? , union results.