PHP & MySQL - get top level category with just one query

I'm trying to get the first category (level zero, no parents) from a child category (which might be any level) with just one query with MySQL. Is that possible?

Here's an example DB structure :

Table categories

    id (INT 11)
    parent (INT 11, zero if it has no parent)
    name

edit: Woul开发者_StackOverflow中文版d MPTT be a easier solution to the problem?

Yes, with a stored function and recursion. You may run into an endless loop, so take care.

It is impossible to scan infinite levels with only one query but if you are sure your categoies tree doesn't go deeper than a certain number of levels you can use this trick, this is for 4 levels:

SELECT COALESCE(c4.parent, c3.parent, c2.parent, c1.parent)
 FROM categories AS c1
 LEFT JOIN categories AS c2 ON( c1.parent = c2.id )
 LEFT JOIN categories AS c3 ON( c2.parent = c3.id )
 LEFT JOIN categories AS c4 ON( c3.parent = c4.id )
 WHERE c1.id = {someID}