Retreive Hierarchical Data (How can I...?)

[StevieK]

I want to make up a (dynamic) HTML page of hyperlinks to look something like this:

|--

http://www.ask.com

|--

http://www.google.com

| |--

http://www.google.com/search?q=cheese

| | |--

http://www.google.com/search?q=cheese+brie

| | |--

http://www.google.com/search?q=cheese+cheddar

| |--

http://www.google.com/search?q=fruit

| |--

http://www.google.com/search?q=fruit+apple

[Figure 1. Daft Example]

The idea is that the links on each branch are somehow associated within that website, and the user will be able to add a link at any level in the tree. There are two database tables to handle this:

-------------- --------------------------
| weblink | | weblinksWeblinks |
-------------- --------------------------
|weblinkID | |childWeblinkID |
|name | |parentWeblinkID |
|URL | |... |
|... | ---------------------------
--------------

The weblink table holds all the hyperlinks, and the weblinksWeblinks table associates one hyperlink with one child hyperlink - if there is one. (n.b. Each hyperlink can have many children, but no more than one parent).

So, the problem is... How can I retrieve all of the hyperlink data in the correct order in one go? It has to include the top-level links (that have no parents in weblinksWeblinks), the bottom-level links (that have no children) and all the others.

I think that the standard approach to this kind of thing is usually to expand each branch only as the user clicks on it (as in a message forum), but to me that smells just as bad as firing a separate

SELECT

statement for each hyperlink you come across: effectively hitting the database 8 times for the daft example data that I've shown above (Once for the top-level hyperlinks, then once for every hyperlink you retrieve after that).

In case you're still not sure what I'm on (about)... If weblink and webLinksWeblinks looked like this...


weblink table
---------------------------------------------------------------------
| weblinkID | name | URL ...
----------------------------------------------------------------------
| 1 | Ask Jeeves |

http://...

| 2 | Google |
| 3 | Google Cheese Search |
| 4 | Google Fruit Search |
| 5 | Google Cheddar Cheese Search|
| 6 | Google Brie Cheese Search |
| 7 | Google Apple Fruit Search |
----------------------------------------------------------------------


weblinksWeblinks table
-----------------------------------------------
| parentWeblinkID | childWeblinkID | ...
-----------------------------------------------
| 2 | 3
| 2 | 4
| 3 | 5
| 3 | 6
| 4 | 7
-----------------------------------------------

... Then the dataset I really want back is this:

------------------------------------------------------------------------------------
| parentWeblinkID | weblinkID | name | URL ...
------------------------------------------------------------------------------------
| NULL | 1 | Ask Jeeves |

http://...

| NULL | 2 | Google |
| 2 | 3 | Google Cheese Search |
| 3 | 5 | Google Cheddar Cheese Search|
| 3 | 6 | Google Brie Cheese Search |
| 2 | 4 | Google Fruit Search |
| 4 | 7 | Google Apple Fruit Search |
------------------------------------------------------------------------------------


If anyone out there knows how to do this, then I'll either bow to your mastery of table joins, or laugh at my own pitiful understanding of them.

Thanks in advance,
Stephen

 

[StevieK]

p.s. I just noticed that there's an ANSI SQL forum, so I've added this post there too, since I (think/hope) that the answer won't need to be tied too closely to MySQL (athough why people insist on making all these different DBMSs so different is beyond me).

The ANSI SQL post is at thread220-375321 for your reading pleasure. Feel free to reply to either post: I just thought I should mention it in case you end up wasting your time typing something that's already been typed.