Return all nodes in many hierarchical trees

Question

Similar to this question: How to execute a query for all nodes between two nodes in a tree?

But I do not have a closing (flattened) table, a child can have many parents, and ID traversal is not necessarily in order. There is no limit to the depth of nesting.

Suppose a circular reference is not possible ... I would like to return all the rows needed to go through the hierarchy.

Suppose the following table:

ParentID    ID    RowNumber(Reference)
1           2     1
2           4     2
4           3     3
3           5     4
1           6     5
6           7     6
2           8     7
3           9     8
1           8     9
6           8     10

Given 1how can I write one query to return all rows (get the relationships of all descendants)?

Similarly, given 2I would expect lines 2,3,4,7,8

Given 6, I would expect lines 6 and 10

, . .

MSAccess SQL Server 2000 +

Answer 1

, , /MPTT . .

, ( ):

AncID  DesID
  1      2
  1      6
  1      4
  1      8
  1      7
  1      3
  1      5
  1      9
  2      4
  2      8
  2      3
  2      5
  2      9
  4      3
  4      5
  4      9
  3      5
  3      9
  6      7

:

SELECT * 
FROM Tbl INNER JOIN Closure ON Tbl.ID=Closure.DesID 
WHERE Closure.AncID = 2

Answer 2

SQL Server: : SQL Server

Jet/MS Access , - . : http://www.mvps.org/access/queries/qry0023.htm

:

, : Lt Rt ( ). , , . . SELECT.

- , , , .

, , , . , , , SELECT- Jet ( MS Access db).

:

ParentID    ID  Lt  Rt  RowNumber(Reference)
Null        1    1  18  0
1           2    2  13  1
2           4    3  10  2
4           3    4   9  3
3           5    5   6  4
1           6   14  17  5
6           7   15  16  6
2           8   11  12  7
3           9    7   8  8

, ID 2:

SELECT * FROM Tbl WHERE Lt Between 2 And 13

, :

Answer 3

, , , ANSI SQL. , Oracle , CONNECT BY, ANSI SQL. CONNECT BY ORI ORARLE SQL SERVER