1

Is it possible to do a match against a query with two tables using a join? The tricky part might be the index on the table but maybe there is a way.. sql is not my strong suit. Many thanks. I imagine it might be something like the following:

SELECT * FROM 'pages' p
LEFT JOIN `tags` t
ON p.id = u.pageid
WHERE MATCH(p.shdescript,t.tag) AGAINST ('romance, relationship')

Many thanks

Mendy
  • 7,612
  • 5
  • 28
  • 42
user1260310
  • 2,229
  • 9
  • 49
  • 67
  • 1
    possible duplicate of [MySQL full text search across multiple tables](http://stackoverflow.com/questions/1117005/mysql-full-text-search-across-multiple-tables) – eggyal May 30 '12 at 19:48
  • 1
    Actually, [this answer](http://stackoverflow.com/a/9951200/623041) to a different question is perhaps more helpful. – eggyal May 30 '12 at 19:50
  • From those two questions, I'm getting you cannot use join but must use union. Do you know if that's right? – user1260310 May 30 '12 at 20:21

1 Answers1

6

It's possible, but you need to have text indexes.

mysql> alter table pages add fulltext index_text(shdescript);

mysql> alter table tags add fulltext index_text(tag);

SELECT * FROM 'pages' p
LEFT JOIN `tags` t
ON p.id = u.pageid
WHERE MATCH(p.shdescript,t.tag) AGAINST ('romance relationship')

I guess that's enough to work.

EDIT:

As of MySQL 5.6 the above fulltext search can be done on the MyISAM & InnoDB storage engines. On earlier MySQL versions only MyISAM tables supported fulltext indexes.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

raiz media
  • 67
  • 5
jcho360
  • 3,724
  • 1
  • 15
  • 24
  • This does not work in MySQL 8. I'm getting `Incorrect arguments to MATCH` error. All match columns must be in the same table: https://stackoverflow.com/a/2610897/2569676 – Mateusz Mar 30 '21 at 22:09