FULLTEXT search over multipe tables??

[seodevhead]

Is it possible to have one FULLTEXT search performed on columns from multiple tables in the same DB? I have a lot of primary->foreign key relationships that need linked and searched in one single FULLTEXT search?

Here is my scenario...

TABLE cars {
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
brand_id SMALLINT UNSIGNED NOT NULL,
color VARCHAR(250) NOT NULL,
description TEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT (color, description)
}

TABLE car_brands {
brand_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
brand_name VARCHAR(250) NOT NULL,
PRIMARY KEY (brand_id),
FULLTEXT (brand_name)
}

As you can see, cars.brand_id=car_brands.brand_id in a query. The problem is, I want one single FULLTEXT search to be performed on the car brand, color and description. But if I do a fulltext search on just the 'cars' table, I can't effectively search the brand names because this table only has brand_id which is a number that links to 'car_brands' table (which contains the linked brand_name). So how do I go about including 'car_brands.brand_name' with 'cars.color' and 'cars.description' in one single FULLTEXT search? Thanks for your help! 🙂

[Roger_Ramjet]

A multi-table full text index? No, not possible.

What you could do is something like this

$SQL = "SELECT cb.brand_name, c.colour, c.description
              FROM cars AS c INNER JOIN car_brands AS cb USING(brand_id)
              WHERE MATCH (cb.brand_name) AGAINST ('" . $bname . "')
              AND MATCH (c.colour) AGAINST ('" . $colour . "')";

PS this would require seperate full text indexes on each table