Recursive table: Get all Childs for given Parent (1 Viewer)

[peng21]

Hallo,

I've a table that looks something like this:

id, parentID, name

ParentId links to id.

Now I would like to get all "child-nodes" for a given parentId.
The recursion goes 4 levels deep.

What I've so far is:

SELECT t1.id, t2.id, t3.id, t4.id
FROM tblPersons AS t4 
    INNER JOIN (tblPersons AS t3 
    INNER JOIN (tblPersons AS t1 
    INNER JOIN tblPersons AS t2 ON t1.id = t2.parentId) ON t3.parentId = t2.id) ON t4.parentId = t3.id
WHERE (t1.id=1234);

Ok that works but the result ist quiet ugly
What I get is something like

id1...id2...id3...id4
1      20    50    51
1      20    50    52
1      20    60    53
1      20    60    54

Now I could use VBA to take a look at each column and store all unique numbers... but is there a more simpler way?

Thanks

 

[jdraw]

Tell us about your database in simple plain English. What is it about? What thing are involved? Do the things interreate with each other? Why? and How?

 

[peng21]

Hm.. I thought my example was simple enough - sorry!

tblPersons ist a Database with employees.

id, reportsTo (=previously called parentId), FirstName

Sample data:

id   reportsTo   FirstName
1    NULL          Peter
2    2             John
3    2             Anna
4    3             Tom
5    NULL          Alice
6    5             Bob

Now I would like to have a query which returns me all "childs" for a given Id.
id=1 should return the numbers 2,3,4.

Hope now that's more clear.

Thanks

 

[spikepl]

Write 4 functions.

Each function has as input an ID and as output either ID or Null. It outputs ID if input ID differs from previous ID or Null if input ID equals previous ID. Internally you declare Static OldID as Long, which retains its value between calls.

Call these functions in your query and display the output of the functions.

I do something like that in another context, exactly to avoid repeats in data.

 

[namliam]

Some SQL along these lines

SELECT YourTable.ID, YourTable.parent
FROM YourTable
WHERE 
   YourTable.ID In (sELECT    iD FROM YourTable WHERE PARENT = 1) 
OR YourTable.ID In (SELECT X1.ID FROM  YourTable AS X INNER JOIN YourTable AS X1 ON X.ID  = X1.parent WHERE X.parent=1)
OR YourTable.ID In (SELECT X2.ID FROM (YourTable AS X INNER JOIN YourTable AS X1 ON X.ID  = X1.parent) 
                                                INNER JOIN YourTable AS X2 ON X1.ID = X2.parent WHERE X.parent=1);

You will have to repeat the nesting in the OR statements as many times as there may be childs.

 

[peng21]

Some SQL along these lines
[...]

Thank you very much!
=> Problem solved


Edit: Can someone tell me please where to mark this thread as "SOLVED"?

 

[namliam]

Despite my years on the forum, no clue how to change it to "Solved"

An alternative is to make a union query, which will probably be a bit more future proof

SELECT X.ID,  X.Parent FROM  Table1 AS X
UNION ALL SELECT X.ID, X1.Parent FROM (Table1 AS X LEFT JOIN Table1 AS X1 ON X.Parent = X1.ID) 
UNION ALL SELECT X.ID, X2.Parent FROM (Table1 AS X LEFT JOIN Table1 AS X1 ON X.Parent = X1.ID) LEFT JOIN Table1 AS X2 ON X1.Parent = X2.ID

Save that as a query and you can always find your parents to any level, offcourse you need to add queries depending on the amount of levels you require

 

[gemma-the-husky]

a couple of points

note that these "hard" solutions will depend on knowing in advance the maximum level of dependency

note also, that I imagine it would be very easy to create a circular relationship that would never resolve

ie tom reports to mike, mike reports to steve, and steve reports to tom