Find Nodes in SQL Tree Structure

I wrote a highly-rated post about a solution I call Closure Table: What is the most efficient/elegant way to parse a flat table into a tree?

I also cover that design in my presentation Models for Hierarchical Data with SQL and PHP and in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

And I've answered questions about hierarchical-data queries many times on Stack Overflow: https://stackoverflow.com/search?q=user%3A20860+%5Bhierarchical-data%5D

Quassnoi has written an epic series of blog articles about querying trees in MySQL: http://explainextended.com/2009/07/22/hierarchial-queries-in-mysql-identifying-trees/

Quassnoi has also answered a number of questions on Stack Overflow about hierarchical-data: https://stackoverflow.com/search?q=user%3A55159+%5Bhierarchical-data%5D

You are using a difficult model for searching, the Adjacency List Model. If you know the number of potential "levels" of parent-to-child relationships you will have, then yes, you can construct a query to do so. However, if it's not a finite number, then it's going to be pretty difficult.

Consider using the Nested Set Model if possible. Here is a good article on the subject:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Back to your question, you would have to join on the same table multiple times to get your desired results, something like this:

SELECT *
FROM YourTable t
    LEFT JOIN YourTable t2 ON t2.parentid = t1.id
    LEFT JOIN YourTable t3 ON t3.parentid = t2.id
    LEFT JOIN YourTable t4 ON t4.parentid = t3.id

With an additional JOIN for each potential child-parent relationship.

Hope this helps and good luck.