recursion - Using MySQL query to traverse rows to make a recursive tree

Question

I have a bill of materials table that is set up like this:
item - parent

The end result when I display the bill of materials is that it is displayed like this:

item 1  - parent 0    
    item 2 - parent 1    
    item 3 - parent 1    

The final result could also be multi level like this:

item 3 - parent 0    
    item 4 - parent 3    
    item 76 - parent 3    

And it can go on ad infinitum:

item 76 - parent 0    
    item 46 - parent 76    

item 46 - parent 0     
    item 25 - parent 46

Right now, I either just get 1 level from the database:

SELECT * FROM bom WHERE parentId = $itemId (shorthand)

Or pull every row from the table and use my recursive function to sort out just the ones I need, but this is obviously inefficient as I may only need 10 rows, but I pull 10,000 records. The output of the recursive function will just create a tree like this:

item 1
   item 2
   item 3
      item 4
      item 76
         item 46
            item 25

All I know is that I am starting at item 1. Item 5 could have a parent of 11; they do not have to go sequential. I want to get all of the child branches in the tree. How could I do this query in mysql?

Answer 1

Back in October 24, 2011, someone posted a question in the DBA StackExchange about tree traversal in MySQL. The SQL for MySQL cannot support it.

I wrote up three(3) Stored Procedures (GetParentIDByID, GetAncestry and GetFamilyTree) in my answer to that question. Hope this information helps you construct what you are looking for.