Tree Query

[Tig]

Question : In Oracle SQL I can do a tree query like below. Can I achieve
the same thing using Acesss 2003 SQL (Jet)? What is the easiet way to
achieve this in Access 2003?

I have a table:

StudentID Name Major GPA TutorId
101 Bill CIS 3.45 102
102 Mary CIS 3.1
103 Sue Marketing 2.95 102
104 Tom Finance 3.5 106
105 Alex CIS 2.75 106
106 Sam Marketing 3.25 103
107 Jane Finance 2.9 102


From above:

a.. Bill tutors Alex, Mary and Sue.
b.. Mary tutors Liz and Ed
c.. Sue tutors Petra
The following example prints a tree structure modeled after the tutoring
relationships in the Students table. Starting with Mary's student id (102)
since no one tutors her.

SELECT LPAD(' ',2*(LEVEL-1)) || students.name
As TutorTree
FROM students
START WITH studentid = '102'
CONNECT BY PRIOR studentid = tutorid;

TUTORTREE
--------------------------------------------------------------------------------
Mary
Bill
Sue
Sam
Tom
Alex
Jane

7 rows selected.
From the tree we can see that Mary tutors Bill, Sue and Jane. In turn, Sue
tutors Sam. Finally, Sam tutors both Tom and Alex.

 

[Tom Ellison]

Dear Tig:

While the data you present IS a tree, the structure of this data does
not guarantee it will be a tree. It is possible to reconstruct this
so it is guaranteed to be a tree (no cycles). That is a separate
topic from what you asked, but it is important to constrain the data
in this way for any serious application.

I mention this because the way I constrain the data will also provide
what you need to perform what you want to do. That is to provide a
"level" column in which the hierarchy of this is represented
structurally.

The level for Mary could be 1, for Bill, Sue, and Jane 2, for Sam 3,
for Tom and Alex 4. By requiring that the level of one's parent
(determined by TutorId) must be less than one's own level, you can
enforce the concept that this is a tree. In fact, that is a necessary
and sufficient condition. Not only does this guarantee a tree, but it
is true of anything that IS a tree.

On the basis of this, I can construct another column in a query which
represents the entire parentage of every row. This is a composite of
some unique key column for every row. I'm going to assume the Name
column is unique. After all, if there is no unique natural key to the
data, then how would a user select any row uniquely given what the
user knows about the entity it represents. Now, I'm assuming the user
doesn't know StudentId, which may not be the case for you. But it
will serve for illustration.

Below, I show each Name, plus the composite key generated in the query
using Name:

Bill Mary Bill
Mary Mary
Sue Mary Sue
Tom Mary Sue Sam Tom
Alex Mary Sue Sam Alex
Sam Mary Sue Sam
Jane Mary Jane

Please notice that, if you sort the above, you achieve exactly the
order of rows you asked for. In addition, I typically indent the
representation of this using those level numbers I assigned, so the
finished product looks like:

Mary
Bill
Sue
Sam
Alex
Tom
Jane

This is exactly your list, but with Alex before Tom (which would seem
quite equivalent, simply being ordered by name while your list was
probably ordered by ID.)

Once you digest this, let me know if you wish to continue, and any
particular comments or question you have.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tig]

Yes, I would liek to continue with your comments.

I am not sure how easy it is to do it.

At the very least, I would like to get the list of names under each student.

eg. if i start traversing with Sam the result of the query should show Alex
and Tom.

The best scenario is also to figure out what level (tree).

For the above example, it woud be
Sam
Alex
Tom

Can this be done via a query?

Thanks in advance!

Tig

 

[Tig]

I have 1 question. What do you mean when you say "constrain" the data?

Is this to prevent "endless loops"?

How would I go about achieving that?

Cheers mate!

Tig

 

[Tom Ellison]

Dear Tig:

Constraining the data is done as I described before.

In Access Jet, this can really only be accomplished though the
application. Using MSDE you could do this in the database engine.

This would prevent cycles. That is a specific mathematical term
probably corresponding to what you call "endless loops."

How you do it depends on whether you're writing for Jet or MSDE, and
whether this approach fits your application practically.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Tig:

This is not a simple thing to produce. It's probably pretty much an
expert level technique.

To get the "list of names under each student" you can use the
"composit key" I suggested. For example, the composite key for Sue is
"Mary Sue" If you filter the table for those beginning with "Mary
Sue" then you will get only Sue and those under her. What I am
proposing will do just this, and much more.

When I have built this, I have determined that it is best to place the
composite key in the table. Usually, it is not best to put derived
data in a table. However, when the derived data is rather complex and
would take considerable processing to produce whenever it is needed,
we sometimes break that rule. In my estimation, this is just such a
case.

We would use a Recordset in a VBA process to do this. Starting from
the Level 1 rows, begin constructing the composite key. The key to
each row goes into the Level 1 slot of the composite key. For level
2, copy the composite key from the level 1 parent and add the key for
the current row to the level 2 slot in the composite key. Continue as
shown through the other levels.

It is also possible to build this using a UNION query. The number of
components to the UNION is (N - 1) ! [factorial] where N is the number
of levels you have. For more than 5 levels, this becomes unwieldy and
slow.

I may be back Sunday, otherwise Monday.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts