#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2008
    Posts
    88
    Rep Power
    10

    Search multiple tables query


    Hi

    I have an access database that i would like to search. Its for a search engine where the user will type in a search term and the program (in asp) will query the database.

    I am struggling to construct the query as not all of the tables are related.

    The tables are constructed as follows:

    Product Pictures
    ProductCode
    HiResPath

    Products
    Productcode
    Datasheetfilename

    News
    newsStory

    Promo
    promoStory

    subcategories
    subcategoryName

    Is there a way to write a query that can search these required tables and fields?

    Any help would be most appreciated.
  2. #2
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    766
    Not with a single query, but in asp you can combine the results of more than one query before sending the results back to the browser. How are you planning to do this, look up the search terms in ALL tables? You could just save the results of 3 or 4 queries in variables, then construct whatever you want to send to the browser. I think I understand all your tables except subcategories, shouldn't that be related to products??
    Experience is the thing you have left when everything else is gone.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2008
    Posts
    88
    Rep Power
    10
    Originally Posted by don94403
    Not with a single query, but in asp you can combine the results of more than one query before sending the results back to the browser. How are you planning to do this, look up the search terms in ALL tables? You could just save the results of 3 or 4 queries in variables, then construct whatever you want to send to the browser. I think I understand all your tables except subcategories, shouldn't that be related to products??
    I think i will take that approach in combining the results of all the queries in asp. Yes I am planning to look up all the search terms in all the tables. Subcategories is related to the products table with the field subcategoryid in both. Is this what you would do?
  6. #4
  7. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    766
    Yes, of course. But you didn't show that, which is why I asked.
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2008
    Posts
    88
    Rep Power
    10
    Originally Posted by don94403
    Yes, of course. But you didn't show that, which is why I asked.
    Thanks for your help don94403, I got it working now.

Similar Threads

  1. how to base query on multiple SELECT results
    By Vandaahl in forum ASP Development
    Replies: 3
    Last Post: May 17th, 2005, 02:48 AM
  2. Search multiple tables
    By ceaseanddesist in forum ASP Development
    Replies: 1
    Last Post: April 29th, 2005, 06:41 AM
  3. Search Form With Multiple Criteria
    By squire in forum Code Bank
    Replies: 10
    Last Post: October 25th, 2004, 07:27 PM
  4. Search Multiple Fields in Multiple Tables
    By Steve Schofield in forum ASP Development
    Replies: 2
    Last Post: December 13th, 2003, 12:01 PM
  5. search multiple tables with sql
    By a1nets in forum SQL Development
    Replies: 1
    Last Post: December 1st, 2003, 06:20 AM

IMN logo majestic logo threadwatch logo seochat tools logo