How to Display a Hierarchy List?

I'm looking for the easiest query in order to display a hierarchy list. Example:

Database has three tables



Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.



Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?

Thanks for your insights.

your schema can be simplified by combining the heading and subheading tables as described here --Categories and Subcategories

the query should be a single, simple query

formatting the results into a nicely indented list is the job of your application language

Thanks, Rudy. That should get me where I'm going. The resource links are pretty good too.


PS. I just ordered your book.


Cheers, and thanks again.

I have a similar issue I think. If I need to create a new post let me know.

The situation I have is there are two basic tables.

1. User table - contains a user ID and a position ID
2. Positions - contains a position ID and parent position ID (who the position reports to)

I want to display the full hierarchy which could be any number of levels deep. A parent could have a child, which could have 3 children, each of which could have 2 children, and one of those has a child, etc. Any number of levels.

I want to create some logic to print these and indent at each level. I realize the indenting can't be done using a simple SQL query but I want to get the correct information to use a front end (ASP.NET or whatever) to do this.

Does this require the use of cursors? If so, how can I tell how many levels deep I need to go (ie - how many nested cursors to use, which I think will be a bad idea)?

For ASP.NET'ers maybe there's a good way to bind this to a TreeView control or something?

Right now I really just need the logic for gathering the data.

NOTE: the schema cannot be changed.

Thanks!

I think you'll find most of your answers in the link r937 provided. It uses an example very much like you're describing. It uses a single table, but you can join your two tables using the same method. You can use almost any language to query then output the data - asp and php are both fine, I use perl myself. To produce the indented format, I think an unordered list <ul> should do the trick.

That example is helpful but it uses the down1, down2, down3, etc so you know that you can have a root and three levels below that, but I don't know how many levels I'll have. It could be 100. How can I adapt the example to fit for me?

nonsense

"Positions - contains a position ID and parent position ID (who the position reports to)"

unless it is the federal government, i don't think there's even a remote chance that you have 100 levels of bosses

Depends on your DBMS.
With Oracle use CONNECT BY to retrieve a hierarchical list.
With Postgres, Firebird or SQL Server use a recursive common table expression.
With other databases you are probably out of luck (the ones I listed are the only ones I know of to support recursive CTE)

Thanks for the worthless reply.

The idea is that there could be ANY number of levels. It should be dynamic! We do have some parts of the "tree" that are 20-25 levels deep. But that's not the point.

Ok. I am using Oracle, so I guess the CONNECT BY would work. I do know that the CONNECT BY does require an index on the column. I'll have to see if that exists. Hopefully it does.

Thanks!

Perhaps we might be a bit more productive here if we couch the question(s) in somewhat of a better context. So, for example, about ten years ago wwwboard from Matt's Script Archive was very popular. It used a threaded list and a ton of recursion to display pages and topics... to the point that displaying a popular topic got quite unruly with subject headings squished hard against the right side of the page. That, among many other factors, led to the development of in-line forum styles that we see today, including this one, which, fortunately, allows a threaded view. So, goto this, for example

http://forums.devshed.com/mysql-help-4/mysql-resources-136196.html

Then, at the top right of the page goto "Display Modes > Switch to Threaded Mode" to see a linked tree. Granted that is not exactly what mateoc15 is asking for but it puts us in an interesting direction. Even though forum posts are displayed 'in-line' so to speak, how a post is recorded depends on how you enter the topic (main link, recent post, or a specific page) and which button you click to reply (a poster's button, the topic button, quick reply), so that the 'tree' appears more varied than you'd think. Now, that particular topic only goes five or six levels deep. How does the forum program generate that tree? Surely, it can't anticipate the number of branches/leaves a thread might have. On another forum, I know of a topic that is over a hundred pages long. How would 'this' forum display a threaded view in that case, especially if the tree had a single branch? (Sorry, I looked for a topic with more posts but didn't find one).

The article linked above, which, by the way, was perfectly suited to my application, discourages query recursion but does not really go into the whys and wherefores of the matter. I'm guessing that in the situation I referred to about a topic with hundreds of pages and thousands of replies query recursion would result in a single query for every post, which probably isn't the best way to go.

That's probably the long way around to making a point. But the forum instance is analogical to what mateoc15 describes. A redesigned database is probably the best solution, but by no means is it the only one. The forum here proves that a dynamic system is possible. I'm just not the guy (yet) to say how to go about it.

[ update - since I can't edit my previous post ]

I found a very long thread here on devshed. Both links take you to the same place.

http://forums.devshed.com/showthread.php?postid=1404352#1404352
http://forums.devshed.com/the-outhouse-138/the-longest-thread-on-devsheds-327568.html

If you switch to threaded view, you can see the tree running off to the right side. Be warned, however, that trying to view the page for me, when set to threaded, prevented the page from displaying properly. I had to turn threaded view off in another thread, then go to the long one, and switch it back on. The point: a dynamic hierarchal tree is definitely possible, even if problematic.

Since my original post above, I've learned that you should probably run the fewest queries possible. That said, recursion, if that is the answer, is possible if you must. One day somebody should look at how vbulletin (or whatever this forum is) handles it.




Lastly, while I'm here, I do not agree this topic should have been moved to oracle. Just because a member decides his concerns are similar to the original post, does not mean the topic should have been hijacked into something it was never meant to be. This was a general sql question and that's where it was posted and where it should have remained. But so be it.