multiple criteria search

Question

I have multiple tables with Customer data (ex Customer Name, Customer Contact Name, Customer Service Item etc).

I need to enable search on these multiple columns across tables. Then I have to use the search result to pull Customer information (I need Customer ID, or Customer Name).

What is the best way to do this?

Possible Solutions:

1. Offer multiple filters (different search boxes), and then handle each result separately. (The client does not prefer this, and wants it in a single box.)
2. Create a temp table (CustomerID, Search Field Values).
3. Create index !?
4. User inner join, and put logic into handling the search result!!!!

Thanks.

Answer 1

Hi,

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

I do believe this similar to Oracle Text Search etc which is used in Oracle applications to allow more intelligent searches, "google-likish".

So it is a fulltext index which is to be created.

Doing it with inner joins (or worse, copying stuff around in temporary tables) might work but the code will be complex and you might kill performance.

Answer 2

the only thing you can do if he customer insists on making a search that works like this is to create a TEXT column, FULLTEXT index it, and concatenate all of the columns you want to search in to this column. if you do this, i suggest that you write your queries in this form to guarantee correct matches while maintining a sort orderthat makes sense:

select *
  from sometable
 where match(search_column) against('$search_terms' in boolean mode)
order
    by match(search_column) against('$search_terms')

Answer 3

try something like:

SELECT
   c.*
    FROM CustomerTable c
        INNER JOIN (SELECT
                        CustomerID
                        FROM Table1
                        WHERE columnA =filter1
                    UNION
                    SELECT
                        CustomerID
                        FROM Table2
                        WHERE columnB =filter2
                    UNION
                    SELECT
                        CustomerID
                        FROM Table3
                        WHERE columnC =filter3
                   ) dt ON c.CustomerID=dt.CustomerID