#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    2
    Rep Power
    0

    Two table query within query


    I have two tables (Access DB), one with product info, other with manuals info like this:

    Table one:

    Cat CDproduct Desc
    1234 1001 Product 1001 belonging to Cat 1234
    1234 1002 Product 1002 belonging to Cat 1234
    5678 1010 Product 1010 belonging to Cat 5678
    5678 9999 Product 9999 belonging to Cat 5678

    Table two:

    CDproduct Manual
    1001 abcd1.pdf
    1001 abcd2.pdf
    1010 abcd3.pdf

    Cat = Product Category, CDproduct = Product Code Indentifier

    So in above table there is no manual for product 9999

    I made a query to get a result matching "Cat", this works fine.
    Now I want to use a single query on "CDproduct" comming from the original query "Cat"with a result that outputs Table one with a match on "Cat" and table 2 with a match on CDproduct, in other words, result should be products from table one, together with manuals from table two (if there is a manual found in table 2). Also products without manuals should be listed.

    Any help in any direction is welcome.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    Please post the code/query you have now.

    You should take a look at the Left Join syntax.
    This will "include all of the records from the first (left - product) of two tables, even if there are no matching values for records in the second (right - manual) table."
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    2
    Rep Power
    0
    Thanks for directing me the right way. Figured it out with LEFT JOIN like you mentioned.

    For those who want to see how, here is the solution which worked OK for me:

    Two tables: All_Products and Documentation
    All_Products table contains main category named MainCategory
    ProdCatID comes from user input, this case a web page.
    Combined JOIN LEFT with WHERE statement to filter out user input.

    "SELECT All_Products.MainCategory, All_Products.ProductID ,Documentation.ProductID " & _
    "FROM All_Products " & _
    "LEFT JOIN Documentation " & _
    "ON All_Products.ProductID=Documentation.ProductID " & _
    "WHERE All_Products.MainCategory ="&ProdCatID&"

    Again thanks for your help MrFujin!

IMN logo majestic logo threadwatch logo seochat tools logo