Hierarchy question/problem...

1. Senior Member
Join Date
Dec 1969
Posts
1,100

## Hierarchy question/problem...

Hi,<BR><BR>I have this table (an example of Bill Willkinson):<BR>-------<BR>Categories <BR> CatID -- CatName <BR> 1 -- Cloth <BR> 2 -- Metal <BR><BR>Subcategories <BR> SubcatID -- CatID -- SubcatName <BR> 1 -- 1 -- Silk <BR> 2 -- 1 -- Cotton <BR> 3 -- 1 -- Wool <BR> 4 -- 2 -- Tin <BR> 5 -- 2 -- Aluminum <BR><BR>Records <BR> RecID -- RecName <BR> 1 -- Pants <BR> 2 -- Shirts <BR> 3 -- Cans <BR><BR>Links <BR> SubcatID -- RecID <BR> 2 -- 1 &#039; pants made out of cotton <BR> 3 -- 1 &#039; pants made out of wool <BR> 1 -- 2 &#039; shirts made out of silk <BR> 2 -- 2 &#039; shirts made out of cotton <BR> 4 -- 3 &#039; tin cans <BR> 5 -- 3 &#039; aluminum cans <BR><BR>-------------<BR><BR>How can I write a hierarchy based of those tables above knowing that a record can have more than one sub-categories.<BR><BR>I want to write the name of the categories an, under it, the sub-categories names where a specified record belongs (the sub-categories may be from different categories).<BR><BR>An example?<BR><BR>Until now I did like this:<BR>- I selected the name of the categories based on the record ID<BR>----------<BR>set rs_cat=db.execute("SELECT products.productid, links.subcatcode_id, links.product_id, subcat.subcatcode, subcat.catcode AS subcat_catcode, categories.catcode AS categories_catcode, categories.catname FROM (categories INNER JOIN subcat ON categories.catcode = subcat.catcode) INNER JOIN (products INNER JOIN links ON products.productid = links.product_id) ON subcat.subcatcode = links.subcatcode_id where productid="& productid &" order by categories.catcode")<BR>----------<BR><BR>Then I do a loop.<BR>In this loop I selected the name of the sub-categories based on the same record ID:<BR>--------<BR>set rs_subcat=db.execute("SELECT products.productid, links.subcatcode_id, links.product_id, subcat.subcatcode, subcat.subname, subcat.catcode FROM subcat INNER JOIN (products INNER JOIN links ON products.productid = links.product_id) ON subcat.subcatcode = links.subcatcode_id where product_id="& productid &" order by subname")<BR>---------<BR><BR>I did a loop here too.<BR>Then I close the loop for categories.<BR>And I close the loop for the sub-categories too.<BR><BR>And what I get is:<BR>----------<BR>kuku (category)<BR> - kuku 1 (sub-category)<BR> - yoko 1<BR>yoko<BR> - kuku 1<BR> - yoko 1<BR>-----------<BR><BR>And want to have:<BR>--------<BR>kuku<BR> - kuku <BR>yoko<BR> - yoko 1<BR>----------<BR><BR> Some indications?<BR>I looked at some hierarchy recordsets (which uses Data Shaping) but I don&#039;t know...should I use it here (if possible - I&#039;m using Access) or should I use something else?<BR><BR>Thanks,<BR>Mircea

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Since you did not give...

...any example tables it&#039;s hard to tell what the right answer(s) should be.<BR><BR>So I will revert to my own sample tables:<BR><BR>Categories <BR>CatID -- CatName <BR>1 -- Cloth <BR>2 -- Metal <BR><BR>Subcategories <BR>SubcatID -- CatID -- SubcatName <BR>1 -- 1 -- Silk <BR>2 -- 1 -- Cotton <BR>3 -- 1 -- Wool <BR>4 -- 2 -- Tin <BR>5 -- 2 -- Aluminum <BR><BR>Records <BR>RecID -- RecName <BR>1 -- Pants <BR>2 -- Shirts <BR>3 -- Cans <BR><BR>Links <BR>SubcatID -- RecID <BR>2 -- 1 &#039; pants made out of cotton <BR>3 -- 1 &#039; pants made out of wool <BR>1 -- 2 &#039; shirts made out of silk <BR>2 -- 2 &#039; shirts made out of cotton <BR>4 -- 3 &#039; tin cans <BR>5 -- 3 &#039; aluminum cans <BR><BR>SO:<BR><BR>SELECT Categories.CatName, Subcategories.SubcatName, Records.RecName<BR>FROM Categories, Subcategories, Records, Links<BR>WHERE Subcategories.catID = Categories.catID<BR> AND Links.subcatID = Subcategories.subcatID<BR> AND Records.recID = Links.recID<BR>ORDER BY CatName, SubcatName, RecName<BR><BR>And that will give you:<BR><BR>Cloth - Cotton - Pants<BR>Cloth - Cotton - Shirts<BR>Cloth - Silk - Shirts<BR>Cloth - Wool - Pants<BR>Metal - Aluminum - Cans<BR>Metal - Tin - Cans<BR><BR>As the output. Which you could easily then report as<BR><BR>Cloth <BR> - Cotton <BR> - Pants<BR> - Shirts<BR>Cloth <BR> - Silk <BR> - Shirts<BR>Cloth <BR> - Wool <BR> - Pants<BR>Metal <BR> - Aluminum <BR> - Cans<BR> - Tin <BR> - Cans<BR><BR>Or any similar format, using the techniques I show in one of the FAQs.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
1,100

## RE: I'm digging :-)...

OK, thanks Bill.<BR><BR>I&#039;m digging now for your FAQ.<BR><BR>Mircea

4. Senior Member
Join Date
Dec 1969
Posts
1,100

## RE: Found it !...

I found your FAQ, Bill.<BR><BR>And I found out what mistake I&#039;ve done by using two recordsets to do what can be done with one recordset.<BR><BR>Thanks,<BR>Mircea<BR><BR>

#### Posting Permissions

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