joins and full-text search

**mPeror** · Jun 1, 2009, 23:20

Hi,

I'm basically trying to perform a join on multiple tables, search the records (after the join), and sort results according to relevance.

I'm not sure how to do this since I calculate the relevance in the beginning of the SELECT statement like this:

Code:

SELECT table_name.*, MATCH(table_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance

Any idea how I can overcome this issue?

**r937** · Jun 2, 2009, 04:45

what exactly is the issue? i don't see any join there...

**mPeror** · Jun 2, 2009, 05:28

Sorry for not explaining it better.

The issue is that I don't know how to include fields from the join in the search. If I do this for example:

Code MySQL:

SELECT table_name.*, MATCH(table_name.field, table2_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance
 
FROM table_name
 
LEFT JOIN table2_name ON table2_name.id = table_name.foreign_key

I get an "unknown column" error.

**longneck** · Jun 2, 2009, 07:30

you can't do that. MATCH() requires all columns to be from the same table.

try this:

Code:

SELECT table_name.*
     , MATCH(table_name.FIELD) AGAINST('something' IN BOOLEAN MODE) * MATCH(table2_name.FIELD) AGAINST('something' IN BOOLEAN MODE) AS relevance
  FROM table_name
LEFT OUTER
  JOIN table2_name
    ON table2_name.id = table_name.foreign_key

**mPeror** · Jun 2, 2009, 08:01

Thanks longneck.

By the way, is that the best way to search MySQL records? I keep duplicating tables (since I use InnoDB) just to use full-text search, and it's getting kind of tiresome.