How to make an sql query to build a tree?

Question

Possible Duplicate:
How to store directory / hierarchy / tree structure in the database?

I use this query:

SELECT Id, Name, ParentId
FROM Table_1

And then I build the tree using all elements.

But what if I want to build a tree that starts from definite Id, so I need to make a query that returns only this Id and all childs of this Id Like this:

SELECT Id, Name, ParentId
FROM Table_1
WHERE (Id = randomNumber) OR (all possible childs of Id = randomNumber)

Help me please to make a query.

EDIT:

This is how you can make it in Oracle

SELECT Id, Name, ParentId
FROM table_1
Connect by prior Id = ParentId
Start with Id = randomNumber

I need MS-Access

You want to get the children of the parentid and the children of children, etc. right? — Marcelo De Zen, Aug 23 '12 at 16:35
Yes, because if it is only one level deep, you can pick up one from those two anawers:) If it is more then you need more, a recursive solution what you can manage with stored procedure f.e. But I got the answer, I wanted to know what devundef asked:) — András Ottó, Aug 23 '12 at 16:44
@András Ottó, it's not only one level deep, it might be more — Steven, Aug 23 '12 at 16:48

score 1 · Answer 1 · answered Aug 23 '12 at 18:26

Try this one, I hope this is what you are looking for:

DECLARE @ID int

SET @ID = 1;

WITH CTE_Table_1
(
 ID,
 Name,
 ParentID,
 TreeLevel
)
AS(
  SELECT 
   ID,
   Name,
   ParentID,
   0 AS TreeLevel
  FROM Table_1
  WHERE ID = @ID

  UNION ALL

  SELECT 
   T.ID,
   T.Name,
   T.ParentID,
   TreeLevel + 1
  FROM Table_1 T
  INNER JOIN CTE_Table_1 ON CTE_Table_1.ID = T.ParentID
)

SELECT * FROM CTE_Table_1

score 0 · Answer 2 · answered Aug 23 '12 at 16:31

0

You can join the table on itself linking the parent id to the child id

SELECT T1.Id, T1.Name, T2.Id, T2.Name
FROM   Table_1 as T1
JOIN   Table_1 as T2 on T1.Id = T2.ParentId

Given the data

Id, Name       ParentId
1,  Top,        null
2,  ChildOne,   1
3,  ChildTwo,   1
4,  ChildThree, 1

This will give you a result like

1,   Top,    2, ChildOne
1,   Top,    3, ChildTwo
1,   Top,    4, ChildFour

answered Aug 23 '12 at 16:31

Ian Quigley

500
1
6
22

And if you have multiple dependences, not just two but three level? You can't get the child of the childs of the given id. – András Ottó Aug 23 '12 at 16:37
If you want it to be recursive, then you'd probably be looking at a recursive list building stored proc? – Ian Quigley Aug 23 '12 at 16:37

score 0 · Answer 3 · answered Aug 23 '12 at 16:33

0

You are almost there, this should do the trick

SELECT Id, Name, ParentId 
FROM Table_1 
WHERE (Id = randomNumber) OR (ParentId = randomNumber)

answered Aug 23 '12 at 16:33

Stuart

1,123
8
24

Wouldn't this only build one level of the tree? – DFord Aug 23 '12 at 16:35
this retuns only root and childs of this root, but where is the childs of the childs ? – Steven Aug 23 '12 at 16:37
danm it, you're right of course. try andras' answer. – Stuart Aug 24 '12 at 12:37

score 0 · Accepted Answer · answered Aug 23 '12 at 23:02

As noted on the related post, this cannot be done using SQL alone, however it can be done using a combination of SQL and VBA relatively easy.

The goal is for a specified Id to return that record and all of its children. As a result, all you need is a boolean function which returns true when the row Id is a direct descendant of the specified ancestor.

Your SQL would be:

SELECT Id, Name, ParentId
FROM Table_1
WHERE IsDescendant(Id, randomNumber);

The VBA function in Access would be:

Public Function IsDescendant(id As Integer, relative As Integer) As Boolean
    Dim currentID As Variant
    currentID = id

    Do Until IsNull(currentID)
        If (currentID = relative) Then
            IsDescendant = True
            Exit Function
        End If
        currentID = DLookup("ParentId", "Table_1", "Id=" & currentID)
    Loop
    IsDescendant = False
End Function

Performance-wise this may not be the greatest because I used DLookup but we could also use RecordSet objects if there are significant performance gains to be had. This is just the quickest code I could jot down.

How to make an sql query to build a tree?

4 Answers4