Jump to content

Work With Recursive Database Relations


Recommended Posts

Hello Community, maybe I'm a little bit over-worked, but I'm stucked in a problem with recursive database queries.

I have a DB table with from/to relations. Here is an excerpt for only one relation (from rel_item_id_from=3 to rel_item_id_to=3):

rel_id	rel_item_id_from	rel_item_id_to
1		52					3
2		3					17

My question is: How can I get a recursive solution to get the upward and downward children? It is also possible to have multiple relations (1:n or n:m).

Thanks for your assistance - OLK

Link to comment
Share on other sites

Well, you could do it... you know... recursively. As in you do one query to find the next step's relations, then another query for the next, and another for the next, until you've gone as far as you want (or you run out of relations). Remember not to check IDs you've already seen before.

Depending on your database server, you may be able to write a recursive SQL query using CTEs...

Link to comment
Share on other sites

Thank you both for these quick and helpful suggestions.

My first goal was to give the heavy work to the Database. Unfortunately I have to dive into the world of "WITH" (CTEs). So I'll try Barands suggestion first.

Wish me luck 😉 and best regards!

Link to comment
Share on other sites

8 hours ago, olk said:

I have to dive into the world of "WITH" (CTEs)

Depends on how many "levels" you need to work with.  

If you only need, say, immediately related parent or child records, or even as far as grandparent or grandchild, you can do that with a regular query, just joining the table to itself the required number of times.  

select ... 
from table1 parent
inner join tabel1 child 
      on parent.child = child.parent
inner join table1 grandchild 
      on child.child = grandchild.parent 

But, as soon as you start getting arbitrary depth of nesting, a CTE is the way to go. 

Also, bear in mind that you want some way of stopping this recursion. 
You have no guarantee that, eventually, someone won't manage to create a loop in your data!  (record1 -> record2 -> record3 -> ... -> record1). 

You can do this in the query itself or, perhaps better, with something (i.e. a Trigger) in the database to detect and reject the creation of such loops.  You might remember to write your query with this Gotcha! in mind, but there's no guarantee that the next Developer to work on this Application will do the same! 

   Phill  W.


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.