SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    New Jersey
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching Two Tables Without A Join

    I have two tables: articles and businesses. They do not share any columns that I can join them on. However, I want to search both of them at the same time from a single text input form. Is this possible with a single query?

    Thanks Much.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, sort of

    you have to select the same number of compatible columns from each table
    Code:
    select 'article '   as source
         , aid          as pkey
         , title        as descr
      from articles
     where title like '%searchterm%'
    union all 
    select 'business' 
         , bus_no
         , bus_name
      from businesses
     where bus_name like '%searchterm%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    New Jersey
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for the reply. I'm new to the union syntax. I tried using:

    Code:
    SELECT  'article_body ' AS source, article_id AS pkey, article_title AS descr
    FROM articles
    WHERE article_title
    LIKE  '%searchterm%'
    UNION  ALL 
    SELECT  'name', bus_id
    FROM businesses
    WHERE name
    LIKE  '%searchterm%'
    LIMIT 0 , 30
    but I'm getting an error near "Union All". Could you explain what is supposed be in the single quote after the SELECT?

    Thank you.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what database? no, wait, let me guess, mysql

    what version? for UNIONs, you need to be on at least 4.0 (the release prior to the current release)

    also, you need to have the same number of columns in each of the SELECTs
    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
  •