accessing parent-child tables using querystrings

SelArom · #1 (**permalink**) 09-20-05, 16:41

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

SelArom · #2 (**permalink**) 09-21-05, 23:02

no thoughts yet? I'm thinking maybe i'll have to redesign my database... is that the best strategy?

-SelArom

r937 · #3 (**permalink**) 09-22-05, 07:48

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

SelArom · #4 (**permalink**) 09-22-05, 10:34

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

Internet Services

Web Development

Digital Marketing

Consumer Tech