SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with join query syntax - one table to two others

    Hope someone can help with this.

    My db structure is:

    tbl_Products

    Product_ID
    Product
    etc
    Category_ID
    SubCategory_ID


    tbl_Categories

    Category_ID
    Category


    tbl_SubCategories

    SubCategory_ID
    SubCategory

    So from that the idea is to join it up so that I can have a query that displays all the Products with a particular Category and SubCategory, but I'm unsure of the syntax to join the main table up to both other tables?

    Any pointers much appreciated.

  2. #2
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I also have a field CategeryLinkID in my SubCategories table, so would it be something like:

    Products >> JOIN >> Categories >> JOIN >> SubCategories

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your tbl_Products table table has two separate foreign key links, one to the tbl_Categories table table and one to the tbl_SubCategories table table

    this is extraneous information in the tbl_Products table table, since your 2nd post indicates that the tbl_SubCategories table table links to the tbl_Categories table table

    so your tbl_Products table table should really only link to the tbl_SubCategories table table

    then your query would look like this --
    Code:
    SELECT ...
      FROM tbl_Products  
    INNER
      JOIN tbl_SubCategories
        ON tbl_SubCategories.SubCategory_ID = tbl_Products.SubCategory_ID
    INNER
      JOIN tbl_Categories
        ON tbl_Categories.Category_ID = tbl_SubCategories.CategeryLinkID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks - that makes sense.

    I assume I need to have a second recordset to select the category ID to start from and match in the join.

    Would that just be to select from the Products table where, for example, CategoryID = 1, then the join query would look up the matching records via the other tables?

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you would just ad that as a WHERE clause in the query.

  6. #6
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - so just:

    Code:
    SELECT ...
      FROM tbl_Products WHERE Category_ID = 1
    INNER
      JOIN tbl_SubCategories
        ON tbl_SubCategories.SubCategory_ID = tbl_Products.SubCategory_ID
    INNER
      JOIN tbl_Categories
        ON tbl_Categories.Category_ID = tbl_SubCategories.CategeryLinkID

  7. #7
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually no, that can't be right, as SubCategories.SubCategory_ID is the ID which needs to determine which records are displayed, and should show the fields from the main Products table, along with the Category from the Categories table, and SubCategory from the SubCategories table.

  8. #8
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so more like:

    Code:
    SELECT ...
      FROM tbl_Products
    INNER
      JOIN tbl_SubCategories
        ON tbl_SubCategories.SubCategory_ID = tbl_Products.SubCategory_ID
    INNER
      JOIN tbl_Categories
        ON tbl_Categories.Category_ID = tbl_SubCategories.CategeryLinkID
    WHERE tbl_SubCategories.SubCategory = 1
    ?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by duklaprague View Post
    ?
    not sure i can help you, because i'm not sure i fully understand the nuances of your table design

    there are anomalies

    for instance, if a product links to subcategory 23 in category B, what happens if subcategory 23 doesn't actually belong to category B?

    that's why i said earlier that your tbl_Products table table should really only link to the tbl_SubCategories table table

    now, if you have some products that only belong to a category, but not to a subcategory, that's another anomaly that needs to be addresses...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, thanks for having a look earlier.

    I was away at the weekend, and have since come back and tried it out, and

    Code:
    SELECT *
      FROM Products 
      INNER
      JOIN subcategories
        ON subcategories.SubCategoryID = Products.SubCategory_ID 
    	INNER
      JOIN categories
        ON categories.category_ID = subcategories.CategoryLinkID
    	WHERE SubCategory_ID = 3
    seems to be doing what I had in mind.

    With the example you mention with subcategory 23 & category B, then it should work as long as the user enters the categories and subcategories correctly in the first place. (I'm using dependent drop downs on the add product page, so that the subcategory list only displays the subcategories relevant to the category selected in its drop down list.

    There are some categories that are not split into subcategories - in those cases I have simply created a single catch all sub category for them.


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
  •