Loop inside loop with record count for each row of the last loop

Question

I have 3 tables: dbo.Category, dbo.SubCategory and dbo.Product with the following structure:

dbo.Category: (Category_ID, Category_name)
dbo.SubCategory: (SubCategory_ID, Category_ID, SubCategory_name)
dbo.Product: (Product_ID, Category_ID, SubCategory_ID, Product_name)

1. I need a loop to display all my categories (from the Category table)
2. I need a second loop (inside the first one) to display all the subcategories belonging to the parent category.
3. I need to display (with record count) how many products have each one of my subcategories.

For example, I need something like this:

Category 1

--- Subcategory1 (7)

--- Subcategory 2 (11)

Category 2

--- Subcategory 5 (88)

--- Subcategory 9 (36)

Category 3

--- Subcategory 8 (0)

--- Subcategory 22 (122)

I am not familiar with classic asp and vbscript and the only thing I have managed to do until now, is only the first loop with my main Categories as following:

<%
dim Connect,RS_test,sql
Set Connect = Server.Createobject("ADODB.Connection")
Connect.Open = MM_sindesi_STRING
sql = "SELECT * FROM dbo.Category"
set RS_test = Connect.Execute(sql)
%>
<%
Do Until RS_test.Eof
%>
<table width="100%" border="1" cellspacing="1" cellpadding="1">
  <tr>
    <td><%=RS_test("Category_name")%></td>
  </tr>
  </table>
<%
RS_test.MoveNext
Loop
%>
<%
Connect.Close() 
Set RS_test = Nothing 
Set Connect = Nothing
%>

The above code works ok but the only thing I am getting are the names of my categories…

I have tried so hard to find some similar questions in stackoverflow but unfortunately I didn’t managed to find anything…

Answer 1

You use nested Do Loop

    <%
    set category = server.createobject("adodb.recordset")
    SQL="SELECT * FROM dbo.Category"
    category.open SQL,Connect,1,3
    %>
    <%i=0%>
    <% Do while not category.eof %>  
<%i=i+1%>
<%=i> Category Record
    <table width="100%" border="1" cellspacing="1" cellpadding="1">
      <tr>
        <td><%=category("Category_name")%></td>
      </tr>
    <%
    cat_id=category(Category_ID)
    set sub_category = server.createobject("adodb.recordset")
    SQL="SELECT * FROM dbo.SubCategory where Category_Id="&cat_id&""
    sub_category.open SQL,Connect,1,3%>
      <%j=0%>
      <% Do while not sub_category.eof %>  
      <%j=j+1%>
    <%=j%> Record Sub Category
       <tr>
        <td><%=category("SubCategory_name")%></td>
      </tr>
          <%
        sub_category.MoveNext
        Loop
        %>  
      </table>




      <%
    category.MoveNext
    Loop
    %>