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

Question

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

Now I want to map the courses relevance based to the sports and to fill courses_sports with this data automatically. It needs two steps.

Collect the data with an apporiate SELECT.
Write the data to the association table.

This post is about the first step. I have some troubles writing the query. What I've tried:

SELECT
    courses.id,
    sports.id
FROM
    courses
JOIN
    coursedata ON coursedata.id = courses.coursedata_id
JOIN
    sports ON MATCH (coursedata.title) AGAINST (sports.title) > 0
-- The test with
-- sports ON MATCH (coursedata.title) AGAINST ('Basketball') > 0
-- works.

This query is not working:

Error Code: 1210

Incorrect arguments to AGAINST

How to implement this 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're courses table doesn't have coursedata_id field to make the join coursedata ON coursedata.id = courses.coursedata_id. By the way what is the common field between sports and courses (or coursedata)? And is course and coursedata exactly the same? — Edper, Apr 20 '13 at 01:15
"_you're courses table doesn't have coursedata_id field_" -- Sorry, it was a typo. See edited post. — automatix, Apr 20 '13 at 01:26
"_By the way what is the common field between sports and courses_" There is no common field between these both tables. It's a many-to-many realized with the association table `courses_sports`. — automatix, Apr 20 '13 at 01:28
thanks now what's the common field between sports and courses (or coursedata)? because in your JOIN sports ON MATCH it should be JOIN sports ON couse.commondfield=sports.commonfield WHERE MATCH.... — Edper, Apr 20 '13 at 01:29
"_is course and coursedata exactly the same?_" They have two "same" -- or better "equal" -- columns (columns with the same content): `title` and `description`. `courses` is `InnoDB`, `coursedata` is `MyISAM`. — automatix, Apr 20 '13 at 01:32
ok, i see that now. that's for fulltext search index. But again what is the common field between sports and courses? — Edper, Apr 20 '13 at 01:43
Please see my [comment](http://stackoverflow.com/questions/16115162/how-to-map-rows-of-two-tables-to-each-other-based-on-the-relevance-in-mysql?noredirect=1#comment23016032_16115162) above. — automatix, Apr 20 '13 at 09:20

Edper · Answer 1 · 2013-04-20T03:03:57.307

You forgot to provide the common field between sports and courses which in this case you join. You also forgot the WHERE statement before the MATCH.

JOIN
    sports ON MATCH (coursedata.title) AGAINST (sports.title) > 0 AND
    sports ON MATCH (coursedata.description) AGAINST (sports.title) > 0

So, it should be like this:

JOIN
    sports ON (course.commonid = sports.commonid) WHERE MATCH

And since you want both the coursedata.title and coursedata.description you could join them in match.

JOIN
    sports ON (course.commonid = sports.commonid) WHERE MATCH(coursedata.title, coursedata.description)

and finally You could not use the field in sports.title because that would mean all the sports title to be compare AGAINST you could probably traverse and put the value inside the AGAINST.

JOIN
    sports ON (course.commonid = sports.commonid) WHERE MATCH(coursedata.title, coursedata.description) AGAINST('Martial')

and probably you could also use BOOLEAN mode because it will not work if you have 50% or more AGAINST matches

JOIN
    sports ON (course.commonid = sports.commonid) WHERE MATCH(coursedata.title, coursedata.description) AGAINST('Martial' IN BOOLEAN MODE)

I have an SQL Fiddle sample but with only two tables, namely, coursedata and sports and added a common field between the two.

AND finally probably you don't have to JOIN sports table but instead traverse on it and then match against? And probably you could just UNION ALL the results.

Thank you for your answer! But I don't intend to `MATCH` `AGAINST` a static `string` (like `MATCH (coursedata.title) AGAINST ('Basketball') > 0`). What I'm trying, is to `MATCH` every `coursedata.title` `AGAINST` every `sports.title`. — automatix, Apr 20 '13 at 10:31

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

1 Answers1