How to get the lowest shared movie for 2 rows in a recursive table (SQL)

Question

Let's say that we have a table with a classic recursive id manager relationship:

Users user_id int manager_id int (refers to user_id)

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

Answer 1

A few minor changes to Quassnoi respond and it works:

WITH
    hier1 (id, parent) AS (
    SELECT      id, parent
    FROM        table
    WHERE       id = @user1
    UNION ALL
    SELECT      id, parent
    FROM        table l, hier1 h
    WHERE       l.id = h.parent
    ),
    hier2 (id, parent) AS (
    SELECT      id, parent
    FROM        table
    WHERE       id = @user2
    UNION ALL
    SELECT      id, parent
    FROM        table l, hier1 h
    WHERE       l.id = h.parent
    )
SELECT  TOP 1 hier1.id
FROM    hier1, hier2
WHERE   hier1.id = hier2.id

Answer 2

WITH
    hier1 (id, parent) AS (
    SELECT  id, parent
    FROM    table l
    WHERE   id = @user1
    UNION ALL
    SELECT  id, parent
    FROM    table l, hier1 h
    WHERE   l.id = parent
    ),
    hier2 (id, parent) AS (
    SELECT  id, parent
    FROM    table l
    WHERE   id = @user2
    UNION ALL
    SELECT  id, parent
    FROM    table l, hier1 h
    WHERE   l.id = parent
    ),
SELECT  TOP 1 hier1.id
FROM    hier1, hier2
WHERE   hier1.id = hier2.id