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! :)

Did you try a LEFT JOIN in your query?



SELECT * FROM cars LEFT JOIN car_brands ON cars.brand_id = car_brands.id WHERE description = var or color = var or brand_name = var;

Why do you have color as varchar?

What else would 'color' be other than VARCHAR? It is a text input column where the user would type in something like 'a metallic grey with a white pin stripe down the side'.

And Korvan, thanks, but I am looking for FULLTEXT search, not the standard LIKE

Yea I re-read your post and I noticed i missed something

I havent tested anything here but either of these might work.


SQL: SELECT * FROM cars LEFT JOIN car_brands ON cars.brand_id = car_brands.id WHERE MATCH (color, description, brand_name) AGAINST ([query]);
or
SQL: SELECT * FROM cars LEFT JOIN car_brands ON cars.brand_id = car_brands.id WHERE MATCH (color, description) AGAINST ([query]) or MATCH (brand_name) AGAINST ([query]);


There is nothing in the documentation about combining JOIN with a FULLTEXT search...

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