Posted by Robert Klemme on 06/21/05 12:47

ryan.mclean@gmail.com wrote:
> Wow, my stab is way off. Hmm, this one is closer:
>
> SELECT B1.NAME_COL, B2.NAME_COL
> FROM TABLE_A A INNER JOIN
> TABLE_B B1 ON A.ID_COL = B1.ID_COL INNER JOIN
> TABLE_B B2 ON A.SUB_ID_COL = B2.ID_COL
> GROUP BY A.SUB_ID_COL, A.ID_COL, B1.NAME_COL, B2.NAME_COL;
>
> There must a be a better way of doing this, I just can't think right
> now :)

Problem is that you need some form of recursion. This can't be done with
standard SQL and CONNECT BY PRIOR exists just in Oracle.

The alternative (that works with SQL Server) is to store the tree
differently. Basically you do an in order tree traversal and store the
counts on node entry and exit. You get something like

create table tree (
no_left int,
no_right int,
node_id int
)

Insert and delete operations will have to properly update these. It's not
overly complicated and not very inefficient.

Kind regards

robert

[Back to original message]