[MYSQL] A recursive query?

[eluser]Clooner[/eluser]
I have the following code to recursively get all the children of a certain category. But when I have a extensive category structure I soon end up with 20+ queries to do a thing that shouldn't be to complex.

Code:

function get_children($parent_id)
{
  $this->db->select('child');
  $this->db->where('parent', $parent_id);
  $query = $this->db->get('category_relations');
  if ($query->num_rows()>0)
  {
    $results=array();
    foreach ($query->result_array() as $row)
    {
      $results[]=$row;
      if ($sub_categories=$this->get_children($row['child']))
        $results=array_merge($results,$sub_categories);
    }
    return $results;
  } else
    return false;
}

Is there an easier way to do this? Preferably within one sql query