When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, General


Question:

I have a database with a table of categories and a table of subcategories. How can I easily make a display that will show each category with its related subcategories grouped under it?


[Print this FAQ]

Answer: This question actually takes many forms, and I've tried to phrase it in as general terms as possible, so that hopefully you will recognize it as being the same as your question. Sample versions of the way this question might be asked include:
-- How can I show all the authors of each book in my catalog?
-- How can I show all the students in each classroom?
-- How can I show all the players on each team?

Before we go further, we need to establish what our database looks like. At a minimum, it contains two tables. The first "outer" or "main" table has a primary key. And the second "inner" or "dependent" table has a field that acts as a foreign key to the primary key in the main table. For example:

Table: Teams
      TeamID -- autonumber field, primary key
      TeamName -- text field
      Wins -- numeric field
      Losses -- numeric field
      ...

Table: Players
      PlayerID -- autonumber field, primary key
      TeamID -- numeric, foreign key to Teams
      PlayerName -- text
      ...


Each of those tables might have many more fields, of course, but those are enough to illustrate the concepts herein. And if you do not have your data arranged in this way--if you have some clumsier organization that doesn't meet the rules of database normalization--then you need to reorganize the data before you start, more than likely. (And do a search with google for "Database Normalization Techniques" if you need help on this topic. There are tons of material on it and it would be redundant to touch on it here!)

Anyway, given those two tables, what can you do with them?

Almost the biggest mistake you can make--and yet the method that we tend to most often see beginners attempting--is to open up the first table and then, as you move to each new record in that table, open up another recordset where you select only the subcategory records that match the given category. Something like this kind of code:

<%
' BAD CODE! DO NOT DO THIS!
Set teamRS = conn.Execute("SELECT * FROM Teams ORDER BY TeamName")
Response.Write "TEAM " & teamRS("TeamName")
Do Until teamRS.EOF
    Set playerRS = conn.Execute("SELECT * FROM Players WHERE TeamID=" & teamRS("TeamID")
    Do Until playerRS.EOF
        Response.Write " -- PLAYER " & playersRS("PlayerName")
        playersRS.MoveNext
    Loop
    playersRS.Close
    teamRS.MoveNext
Loop
%>


This works, but it puts a pretty big strain on the performance of your database server! If you have 30 teams, you have to open up 31 recordsets.

Let's see if we can do it all with one recordset!

<%
SQL = "SELECT * FROM Teams, Players " _
        & "WHERE Players.TeamID = Teams.TeamID " _
        & "ORDER BY Teams.TeamName, Players.PlayerName"
Set RS = conn.Execute( SQL )

priorTeam = ""
Do Until RS.EOF
    curTeam = RS("TeamName")
    If curTeam <> priorTeam Then
        If priorTeam <> "" Then
            Response.Write "</UL>"
        End If
        Response.Write "TEAM: " & curTeam & "<UL>"
        priorTeam = curTeam
    End If
    Response.Write "<LI>" & RS("PlayerName")
    RS.MoveNext
Loop
Response.Write "</UL>" ' clean up the tags!
%>


Let's look at how that code works.

First, we use a JOIN to get the information from both tables at once. (Yes, that is a JOIN. If you are an Access user who is used to seeing the keywords INNER JOIN, be assured that this is simply another way of expressing the same thing.) The important thing here is the ORDER BY clause that we put there! By ordering first by team name and then by player name, we ensure that all the players in the "Angels" will appear before all the players in the "Mariners". And, further, all the players will appear in alphabetical order within the team grouping.

We get an ADODB.RecordSet with all the players and all the teams and we are ready to process it. First, though, we initialize our priorTeam variable to a blank string.

We start with the first record. We get the name of the team from the record. Does it match the name of the prior team? (Well, of course not! There isn't any prior team!) A special test: Is the name of the prior team blank? Yes? Then do nothing. In any case, since we are processing a new team, output the team name along with an opening list delimiter. And then "remember" that this is the prior team! And we're done with the change-of-team processing.

So we output the name of the player that this record refers to and go to the next record.

Back at the top of our loop: We get the name of the team from the record. Does it match the name of the prior team? Let's assume it does. So we skip the change-of-team code and go to the next record. And this paragraph repeats until we finish the first team.

Finally, we get to a record where the team is not the same as the one we remembered in priorTeam. Is the name of the prior team blank? Not any more, so output a closing list delimiter tag. And, again, we output the new team name and an opening list delimiter and proceed to work with the players of this next team.

And we continue all this until we run out of records.

See that? It's not hard! You only had to keep track of one extra thing: the name of the team the prior player in the recordset was a member of. This is clean, simple, and fast code. In almost all cases, there is no reason not to go this way.

"He said 'almost all'! What is he hiding!"

Suppose that you have huge amount of information stored in each record of the "outer" (or "main") table. Perhaps a lengthy history of the team. Or maybe a ".gif" image of the team picture. With the technique given above, you'll be duplicating that huge chunk of data in each record of the recordset! This is not a good thing. What can we do?

The simple answer is that we can do part of the JOIN operation ourself, in VBScript coding!

<%
' add this recordset!
teamSQL = "SELECT * FROM Teams ORDER BY Teams.TeamName"
Set teamRS = conn.Execute( teamSQL )

' same as before except we only get one field from Team...
playerSQL = "SELECT Teams.TeamName, Players.* " _
        & "FROM Teams, Players " _
        & "WHERE Players.TeamID = Teams.TeamID " _
        & "ORDER BY Teams.TeamName, Players.PlayerName"
Set RS = conn.Execute( playerSQL )

priorTeam = ""
Do Until RS.EOF
    curTeam = RS("TeamName")
    If curTeam <> priorTeam Then
        If priorTeam <> "" Then
            Response.Write "</UL>"
        End If
        Response.Write "TEAM: " & curTeam & "<UL>"
        priorTeam = curTeam
        ' AND HERE IS THE NEW CODE!
        If curTeam <> teamRS("TeamName") Then
            Response.Write "Something went badly wrong!"
            Response.End
        End If
        ' (field names are just for demo purposes)
        Response.Write "This team was established in " _
                & teamRS("startYear") _
                & " by " & teamRS("founder") _
                & " and its history is " _
                & teamRS("history") & "<P>"
        teamRS.MoveNext
        ' end of added code
    End If
    Response.Write "<LI>" & RS("PlayerName")
    RS.MoveNext
Loop
Response.Write "</UL>" ' clean up the tags!
%>


See what we've done? We've ensured that the primary sort in both recordsets (the name of the team, in this example) is the same. And then we have limited the data we get from the "outer" table to just the one field (again, the name of the team). Then, as we traipse through the RS recordset in exactly the same manner as before, we pull the needed extra information from the main table only as we change teams! And we only do the teamRS.MoveNext at that time, so (if we did everything right!) we will never get that error message and end prematurely.

Whew! If it looks complicated to you on first glance, study it a while longer. It really isn't that hard to adapt to whatever situation you might need it for.

If you'd like to see an example of the first and simpler form of this code, go to http://www.ClearviewDesign.com/NEWBIE and look for the "Special Category and Subcategory Display Demo" on that page.


FAQ posted by Bill Wilkinson at 4/20/2001 10:29:26 PM to the Databases, General category. This FAQ has been viewed 64,205 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article