View Full Version : Searching by relevancy on multiple tables

04-28-2009, 11:53 AM
I have 3 tables in my database (de_en,es_en,la_en), each a dictionary. Each has four columns (id,order,lang1,lang2 - lang1 and lang2 are replaced with the iso code for that language). I'm trying to build a query that will search a particular entry on all of the tables (expect to be adding more too), and sort them by relevancy. The lang1 and lang2 columns are fulltext indexes.

This is what I've created for a single table.

$q = 'word';

"SELECT *, MATCH (en) AGAINST ('" . $q . "') AS score FROM de_en WHERE MATCH (en) AGAINST ('" . $q . "') ORDER BY score DESC"

I am unsure if or how to implement a join or union here, or if I should just query each table separately?