recursive join to the same entity :/

transaud

Heya,

anyone know a single select query to do the following :

I'm currently working on a scottish heritage sql database. the table I'm trying to optimise looks something like

p-node c-node

0 --------- 1

1 --------- 2

1 --------- 3

2 --------- 4

2 --------- 5

3 --------- 6

3 --------- 7

3 --------- 8

5 --------- 9

What I need to do is build a heritage tree from these nodes(similiar to a b-tree), without performing many select one 2 many joins per each parent node

TTT

i got same problem and i have found óne solution, but works MsSql only
http://www.sqlteam.com/item.asp?ItemID=1353

with queries this should be done like this,but problem is odbc_connection that should be closed if next fetch_row begins ... and then how to get back to bottom of tree ??
let me know if you get this work.

$count = 0;
$q[$count]select * from table where parent = 0
while {

  include("page2.php");
     //page 2//
     $count ++;
      $q[$count] =select * from table where parent = odbc_result($q[$count-1,"id"])
        while(odbc_num_rows($q[$count]) == -1)
          {
             include(page2.php);
          }
        if($q[$count] != -1)
         {
             $count--;
         }

}

chriskl

In postgres you can write a nice little stored procedure that will do it for you quite easily. You can also use (in the new 7.3 version) contrib/ltree and contrib/connectby to do it. The first lets you index hierarchies (so sweet!) and the second simulates Oracle's CONNECT BY clause.

If you use MySQL you're screwed 😉

Chris