To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q022000-2.shtml

The SQL Guru Answers your Questions...


I am kinda new at this whole ASP thing, but here is what I am trying to do. I have an access database that is laid out as follows:

ID	Name		Supervisor
1	Mike		0
2	Dave		1
3	Gary		1
4	Sally		2
5	Mary		2
6	Susan		3
7	Ben			3

Here is what I am trying to accomplish:

If I enter an ID number, I would like to view all of the names that have that ID as a Supervisor and all of the names below that.

For example, if I enter 3 (Gary), I would get Susan and Ben. However, if I enter 1 (Mike), I would get Dave, then Sally and Mary; and get Gary then Susan and Ben.

Do you have any advice for me from an ASP and SQL point of view? Thanks for your help!

Heh, we've talked about this before, I believe. What you're trying to do is fully traverse a tree structure that's "hidden" in the database table. Here is a link to the last question that dealt with this topic:

  • http://www.4guysfromrolla.com/webtech/sqlguru/q121799-1.shtml

    However, you're somewhat limited because you're using MS Access as your database. Access has a much less robust SQL language than, say, SQL Server, so you're pretty limited in what you can do. How about:

      1.) Calculate and store each person's "depth" in the tree. In other words, big boss man, who reports to no one (except possibly the stockholders :) has a depth of 0, Prez has a depth of 1, etc. Be careful, you'll need to recalculate as the data changes.

      2.) When performing this query, pull back every employee below the queried employee's depth.

      3.) Write some magic ASP code to make the results pretty. (Sorry, can't help ya much with this one, but you're basically looping through the recordset a bunch to traverse the tree.)

    Good luck!
    Sean


  • Article Information
    Article Title: SQL Guru: Traversing a Tree-like Database Table Using Access
    Article Author: Scott Mitchell
    Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q022000-2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers