Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Location
    EIB Network
    Posts
    474

    * FULLTEXT search over multiple tables??

    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...

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

    TABLE car_brands {
    brand_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    brand_name VARCHAR(250NOT 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!

  2. #2
    Join Date
    Nov 2005
    Posts
    268
    Did you try a LEFT JOIN in your query?


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

  3. #3
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Why do you have color as varchar?
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  4. #4
    Join Date
    Aug 2005
    Location
    EIB Network
    Posts
    474
    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

  5. #5
    Join Date
    Nov 2005
    Posts
    268
    Yea I re-read your post and I noticed i missed something

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

    Code:
     
    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/...xt-search.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •