SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query for a Search Engine

    Hi

    I am trying to construct a search engine in asp using an access database.

    I have 3 (possibly 1 or 2 more in the future) tables that need querying:

    Images
    ProductCode
    HiRes

    Products
    ProductsURL
    ProductCode
    ProductTitle
    ProductDescription
    ProductSpec

    Resources
    resourcesURL
    ProductCode
    DataSheet
    HandBook
    Drivers1


    I have tried a union all approach but it only returns the field HiRes:
    Code:
    SELECT HiRes
    FROM Images
    WHERE 
    ((ProductCode Like '*sftgsfghf*') or (HiRes Like '*sftgsfghf*'))
    UNION
    SELECT productsURL  
    FROM Products
    WHERE 
    ((ProductCode Like '*sftgsfghf*') or (ProductTitle Like '*sftgsfghf*') or (ProductDescription Like '*sftgsfghf*') or (ProductSpec Like '*sftgsfghf*'))
    UNION
    SELECT resourcesURL  
    FROM Resources
    WHERE 
    ((ProductCode Like '*sftgsfghf*') or (DataSheet Like '*sftgsfghf*') or (HandBook Like '*sftgsfghf*') or (Drivers1 Like '*sftgsfghf*'))

    I need to return HiRes, productsURL, resourcesURL. Any ideas how I can achieve this or is there another approach?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by robert475 View Post
    I have tried a union all approach but it only returns the field HiRes:
    actually, it does return everything you want, it's just that in a union query, the result columns are named according to the first subselect in the union

    let me change your query slightly for you...
    Code:
    SELECT 'images' AS source_table
         , HiRes AS found_column
      FROM Images
     WHERE ProductCode LIKE '*sftgsfghf*'
        OR HiRes LIKE '*sftgsfghf*'
    UNION ALL
    SELECT 'products'
         , productsURL  
      FROM Products
     WHERE ProductCode LIKE '*sftgsfghf*'
        OR ProductTitle LIKE '*sftgsfghf*'
        OR ProductDescription LIKE '*sftgsfghf*'
        OR ProductSpec LIKE '*sftgsfghf*'
    UNION ALL
    SELECT 'resources
         , resourcesURL  
     FROM Resources
     WHERE ProductCode LIKE '*sftgsfghf*'
        OR DataSheet LIKE '*sftgsfghf*'
        OR HandBook LIKE '*sftgsfghf*'
        OR Drivers1 LIKE '*sftgsfghf*'
    i added a new column to identify which table the row was found in, and changed UNION to UNION ALL (to avoid an unnecessary and expensive sort)

    i also removed those stupid unnecessary parentheses that access loves so much, but it's gonna put them back when you save the query

    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
  •