Hierarchical queries in MySQL

Question

I'm trying to find all the parents, grandparents, etc. of a particular field with any depth. For example, given the below structure, if I provide 5, the values returned should be 1, 2, 3 and 4.

| a  | b  |
-----------
| 1  | 2  |
| 2  | 3  |
| 3  | 4  |
| 4  | 5  |
| 3  | 6  |
| 4  | 7  |

How would I do this?

Answer 1

The following answer is not MYSQL-only, but uses PHP. This answer can be useful for all those that end up on this page during their search (as I did) but are not limited to using MYSQL only.

If you have a database with a nested structure of unknown depth, you can print out the contents using a recursive loop:

function goDownALevel($parent){
     $children = $parent->getChildren(); //underlying SQL function
     if($children != null){
          foreach($children as $child){
                //Print the child content here
                goDownALevel($child);
          }
     }
}

This function can also be rewritten in any other language like Javascript.

Answer 2

SELECT  @id :=
        (
        SELECT  senderid
        FROM    mytable
        WHERE   receiverid = @id
        ) AS person
FROM    (
        SELECT  @id := 5
        ) vars
STRAIGHT_JOIN
        mytable
WHERE   @id IS NOT NULL