SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question joins and full-text search

    Hi,

    I'm basically trying to perform a join on multiple tables, search the records (after the join), and sort results according to relevance.

    I'm not sure how to do this since I calculate the relevance in the beginning of the SELECT statement like this:

    Code:
    SELECT table_name.*, MATCH(table_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance
    Any idea how I can overcome this issue?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what exactly is the issue? i don't see any join there...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for not explaining it better.

    The issue is that I don't know how to include fields from the join in the search. If I do this for example:

    Code MySQL:
    SELECT table_name.*, MATCH(table_name.field, table2_name.field) AGAINST('something' IN BOOLEAN MODE) AS relevance
     
    FROM table_name
     
    LEFT JOIN table2_name ON table2_name.id = table_name.foreign_key

    I get an "unknown column" error.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you can't do that. MATCH() requires all columns to be from the same table.

    try this:
    Code:
    SELECT table_name.*
         , MATCH(table_name.FIELD) AGAINST('something' IN BOOLEAN MODE) * MATCH(table2_name.FIELD) AGAINST('something' IN BOOLEAN MODE) AS relevance
      FROM table_name
    LEFT OUTER
      JOIN table2_name
        ON table2_name.id = table_name.foreign_key
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks longneck.

    By the way, is that the best way to search MySQL records? I keep duplicating tables (since I use InnoDB) just to use full-text search, and it's getting kind of tiresome.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mPeror View Post
    I keep duplicating tables (since I use InnoDB) just to use full-text search, and it's getting kind of tiresome.
    perhaps be more judicious about when to use fulltext search and when to use plain old LIKE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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