Given a table with two columns:
Where each thread_subid could be a thread_id of another row. How do i create a sql to select a thread_is and all of its thread_subid nesting at different levels?
Unless there is a support in MySQL for hierarchical queries similar to the one in Oracle (and I doubt there is), it generally cannot be done. It theoretically might be done in pure SQL if the maximum depth (number of levels) is known beforehand (before constructing the query). The reason is you need one self-join (parent-child) for every level and if you don't know how many levels there can be, you obviously cannot build that query. However, I don't know whether such query would be practical (ie. efficient) in MySQL - or in any other database.