When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right
Print this page.
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:

  • https://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!

    Read Other SQL Guru Questions

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