EXPLAIN EXTENDED

From Stack Overflow:

Let's say that we have we have a table with the classic manager_id recursive relationship:

Users (user_id int, manager_id int) (refers to user_id)

If you randomly select 2 rows in the table, or 2 nodes, how do you find the lowest level common ancestor? My platform is SQL Server 2005 (Transact-SQL), but any ANSI compliant SQL will also work...

Very nice question.

This may be useful to check against any kind of common ancestry in a tree structure: classes, folders, etc.
Read the rest of this entry »