SQL / MySQL recursively pull from the same table

Question

This problem is a strange one and I think I am simply just thinking about this wrong. I am trying to get (recursively) all page URL's associated with a given contractor. The three relevant tables are:

|-------------------------------------------------|
|                 client_sections                 |
|-------------------------------------------------|
|    contractor_id    |    reusable_section_id    |
|-------------------------------------------------|

|--------------------------------------------------------|
|                     reusable_sections                  |
|--------------------------------------------------------|
|    reusable_category_id    |    reusable_section_id    |
|--------------------------------------------------------|

|--------------------------------------------|
|             reusable_content               |
|--------------------------------------------|
|    reusable_section_id    |    page_url    |
|--------------------------------------------|

Now here is the need -- The first query is

SELECT reusable_section_id FROM client_sections where contractor_id = '119'

This gets all the top level "parent sections" -- Which is reusable_category_id in the table reusable_sections. If I had to stop there I would have it handled. But the caveat that I am running into is that each of those returned sections "could" be a parent section as well. I have tried a nested SELECT and the result was nothing short of disastrous!

What I am attempting:

» Get contractor parent sections by contractor ID from client_sections table

    » get sections by reusable_category_id = reusable_section_id in reusable_sections  -- 

        » get page_url for all the sections returned (including the initial parents) 
          by reusable_section_id in reusable_content  -- I am good until this point

            » recur the last two steps with the current reusable_section_id as 
              reusable_category_id to check for children  (!!#$@#!!!)

                » Finally return all pages for all sections under that contractor_id

I have attempted queries ranging from crazy JOINSs to nested SELECTs to no avail. I have read many posts including This One. I am a competent SQL developer, I think my logic is flawed and a simple point in the right direction may just spark my creativity! Is the best way a WHILE loop? Nested SELECT? JOIN? My brain is fried! Help?

How many levels of recursion do you expect? If you don't have a limit, you cannot solve this problem simply by doing left outer joins. You will need a stored procedure, or you will have to implement it using a programming language. It is basically a tree. — Menelaos, Aug 24 '17 at 16:16
Theoretically it could be infinite .. That's why the recursion has me stumped .. I could just do it with JOINS otherwise .. — Zak, Aug 24 '17 at 16:20
https://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree :D . See this: https://www.slideshare.net/billkarwin/models-for-hierarchical-data — Menelaos, Aug 24 '17 at 16:20

score 1 · Accepted Answer · answered Aug 24 '17 at 16:17

What you have seems like a tree. If you are talking about navigating through a finite number of levels, than a finite number of left outer joins will be enough.

However, if you have an infinite number of possible levels, I do not think you can solve your problem using plain old SQL and joins.

You will have to resort to a stored procedure, or a programming language (php, java, etc).

However, it seems that people have implemented stored procedures on how to traverse through a tree, so you can go through that approach. Example:

Using MySQL query to traverse rows to make a recursive tree

Stored procedure was the trick ... That was a great read .. Thanks! — Zak, Aug 24 '17 at 20:07

SQL / MySQL recursively pull from the same table

1 Answers1