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 3Here 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:
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
| Read Other SQL Guru Questions |
