Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    20

    Unhappy accessing parent-child tables using querystrings

    hi! I had been here before and received some great help. now i'm stuck again. I designed a table for categories, and made it so that I can create subcategories using the structure below:

    catID
    catName
    catParent

    but i've run across a problem... I want to make a web page for accessing the products in the database, and want to vary the products selected based on a querystring with the catID. the problem is I want to be able to choose either a main category or a subcategory.

    for example, if I want to select specifically card magic items, I send catID=101. However if i want to get ALL magic items the category code is 1. but if I send catID=1. However, the categorycode stored with the products is only the subcategory (like 101)... so is the solution to also store the maincatID with the product? that seems redundant because the catParent is stored with the category in the category table...

    i'm confused, does anybody have an idea of what I can do to improve this situation? thanks in advance!

    -SelArom
    Last edited by SelArom; 09-20-05 at 17:47.

  2. #2
    Join Date
    Jul 2005
    Posts
    20
    no thoughts yet? I'm thinking maybe i'll have to redesign my database... is that the best strategy?

    -SelArom

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your database is fine

    when you send a catID, the query that you run should return not only products in that category, but also products in that category's subcategories ...
    Code:
    /* programs attached to the category */
    select cat.catID
         , cat.catName
         , programs.programname
      from categories as cat
    left outer
      join programs
        on cat.catID
         = programs.catID  
     where cat.catID = 937     
    union all
    /* programs attached to the subcategory */
    select subcat.catID
         , subcat.catName
         , programs.programname
      from categories as cat
    left outer
      join categories as subcat
        on cat.catID
         = subcat.catParent    
    left outer
      join programs
        on subcat.catID
         = programs.catID  
     where cat.catID = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2005
    Posts
    20
    Quote Originally Posted by r937
    your database is fine

    when you send a catID, the query that you run should return not only products in that category, but also products in that category's subcategories ...
    Code:
    /* programs attached to the category */
    select cat.catID
         , cat.catName
         , programs.programname
      from categories as cat
    left outer
      join programs
        on cat.catID
         = programs.catID  
     where cat.catID = 937     
    union all
    /* programs attached to the subcategory */
    select subcat.catID
         , subcat.catName
         , programs.programname
      from categories as cat
    left outer
      join categories as subcat
        on cat.catID
         = subcat.catParent    
    left outer
      join programs
        on subcat.catID
         = programs.catID  
     where cat.catID = 937
    Thank you for your reply! I'm playing with the query seeing if I can get it to work... however there are no products with the category set to a main category. What I mean is, only the subcategorycode is stored with the product, so if I send say categoryCode=1 (that is the categorycode for magic), I need to get all the products whose subcategorycode's PARENT is 1. this is really confusing, so if the query you gave me does that I'm not seeing it i will keep playing with it but if I need to do something different I would appreciate the informaiton! thanks again!

    -Selarom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •