FULLTEXT over multipe tables

Is there a way to perform a fulltext search over multiple tables?

[SEOdevhead]

Is there anyway to perform a fulltext search over columns from more than one table? I have some highly normalized tables that need to be linked a searched and I want to use fulltext to do it. Any ideas? Thanks.

[aspdaddy]

What kind of columns and data types you you searching, are they indexed or unique like part numbers , order numbers, zip codes, Or is it searching descriptions?

If its descriptions do you need wildcard before, after or both and approx how many rows and columns do you have?

[SEOdevhead]

Well... no wildcards needed since this will be a fulltext search. I have a cars database that is normalized to the point where the brand name (ie Ford) is in one table and the car model (ie. Taurus) is in another. Of course the tables are joined but I need my users to be able to do a single fulltext search and allow the fulltext search to search both car brand and car model (which exist in two seperate tables).

[aspdaddy]

This should do it:

select * from vCars where Makes.Name=@search or Models.Name=@search

Where vCars is a View:
Select Cars.ID,Makes.Name,Models.Name from Cars inner join Makes on Cars.MakeID = Makes.ID inner join Models on Cars.ModelID = Model.ID

I dont quite know what you mean by fulltext search? You can either search using exact match using = like above or you can use wildcards for a partial matching

[SEOdevhead]

Fulltext as in fulltext indexes. I am using MySQL.

[aspdaddy]

Indeexes are just physical design / optimisation , it doesnt really affect how you write your queries for this particular problem.

[FalseDawn]

I assume you've trawled through this page:

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

and all the user comments? I remember seeing something in there about how it can be done - I think you either need to create fulltext indexes on every column and merge the results, or merge the column data into one "big" column and create your fulltext index on that.

[arran]

Indeexes are just physical design / optimisation , it doesnt really affect how you write your queries for this particular problem.

aspdaddy, the OP is talking about full-text indexing (i.e. MySQL functionality which allows you to add some sort of search facility to your website) not normal indexing (for performance benefits).

I think the only solution is to have one (full-text) index on each table, execute the same query on each table and merge the resultsets using union. Finally, sort the combined resultset by the relevance column.

arran.