----- Original Message -----
From: "Jonathan Stimmel" <stimmj@stripped>
To: <mysql@stripped>
Sent: Friday, January 07, 2000 12:12 PM
Subject: "recursive" select?
> I imagine this falls under a FAQ, but I have been unsuccessful at finding
> a concrete answer to my situation. I also suspect that this is not
> possible with MySQL (currently using 3.22.27) without compiling and
> linking a new function into the server, but I'm still very new to SQL,
> so...
>
> I have a hierarchy/tree stored in a table, something like this:
>
[snip]
>
> +----------------+----------------------+---------------------------+
> | CategoryNumber | ParentCategoryNumber | CategoryName |
> +----------------+----------------------+---------------------------+
> | 15101 | 0 | Top Category |
> | 15171 | 15101 | Middle Category |
> | 15181 | 15171 | Current Category |
> +----------------+----------------------+---------------------------+
> Jonathan Stimmel
Hi Jonathan,
You can find a recent thread in the archives on this topic:
http://www.progressive-comp.com/Lists/?l=mysql&w=2&r=1&s=masochist&q=b
During that thread, someone also mentioned it had come up before .. but I
never tracked down the earlier thread.
Sub-selects aren't implemented until >= 3.23 .. if that was one of your
questions.
I've done considerable fumbling on this topic, trying everything from [root,
parent, node] or 50 columns of zerofill int's to account for the depth of
the node while using CONCAT(a, '.', b), etc.. etc..
What I (and the originator of that thread) have concluded is that the most
optimal way (without sub-selects) is to wrangle a char column of period
delimited zerofill'ed numbers.
I've also chosen to keep a seperate column indicating the the root id, such
as this:
mysql> SELECT * FROM tree ORDER BY id_m;
+-------+---------------+------+
| id_t | id_m | cid |
+-------+---------------+------+
| 00001 | 00001. | 1 |
| 00001 | 00001.00001. | 2 |
...
| 00001 | 00001.00002. | 9 |
| 00002 | 00002. | 5 |
The limitation is that it can only go about 42.5 nodes deep ( indexed
char(255)/6 ), but still 99,999 "down"
In the end, I determined this was the only possible way to do it - although
I tried very hard to find otherwise.
But, it does accomplish everything in a single select, and you can even play
with LIKE "00001._____." or count '.' characters to limit the depth (I spose
ya could just keep a depth column for simplicity).
I have yet to install any of the 3.23 releases, and would be interested to
see the performance difference on this method vs. sub-selects.
Hope this helps,
-Jay J
