SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Two "From" Clauses in Select Statement-help

    Hi All,

    Is it possible to write two "from" clauses in a select statement, so that the search queries two tables? Or, do I need a join statement in here?
    The submitted form in html has two text input boxes for keywords. I want each input box to search different tables.

    My example:

    Code:
    "SELECT * FROM table_1 WHERE MATCH(column) AGAINST("word_1" IN BOOLEAN MODE) 
    
    AND SELECT * FROM table_2 WHERE MATCH(column) AGAINST("word_2" IN BOOLEAN MODE)"
    thanks in advance

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you want is a UNION. Forms a recordset from two select statements.

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Philip -

    So my statement should look something like:

    Code:
    (SELECT * FROM table_1 WHERE MATCH(column) AGAINST("word_1*" IN BOOLEAN MODE))
    UNION
    (SELECT * FROM table_2 WHERE MATCH(column) AGAINST("word_2*" IN BOOLEAN MODE))
    I am assuming that this will work for full-text searching (?).

    Cheers

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't believe you need the brackets around the SELECTs.

    There are restriction on UNIONs in that you are forming a single recordset so the columns from the two tables should have the same data type.

    Can't imagine a reason that full-text searching should be a problem.

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So both columns in each select statement must be, for example, TEXT ?

    Is this what you mean by the same "data type"?

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They can be any data type as long as they are the same data type. So if one is TEXT the other must be TEXT as well.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Both SELECTs must return the same number of columns as well (I know that this is implicit in your 'same type' rule, but it can't hurt saying it explicitly )

    The best thing is not to use '*', but name each column. That way, if for some reason in the future a column is added to one of the tables, the UNION will continue to work.

  8. #8
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to both of you - I'll start working on my script...

    Cheers!


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
  •