Finding ancestors in a tree using CTE

Hi All,
I have a table thats organized as follows
5
|--2
| |--3
| | |--8
| | |--9
| |
| |--6
|
|--1
|--4
|--7
EmployeeID BossID
8 3
9 3
3 2
6 2
2 5
4 1
7 1
1 5
5 5
Note that 5 is his own boss.
Getting children of a node is fairly trivial (theres an excellent
explanation on MSDN and other books), but I'm struggling with getting
the ancestors
Given 3, I would like to get all the ancestors of it (in this case 2
and 5)
I'm using SQL 2005 and would like to use CTEdeclare @.empID int;
set @.empID = 3;
with t as (select empID, bossID from emp where empID = @.empID
union all
select emp.empID, emp.bossID
from emp join t on emp.empID = t.bossID
where emp.empID <> emp.bossID)
select bossID from t;
Linchi
"sprash25@.gmail.com" wrote:

> Hi All,
> I have a table thats organized as follows
> 5
> |--2
> | |--3
> | | |--8
> | | |--9
> | |
> | |--6
> |
> |--1
> |--4
> |--7
>
> EmployeeID BossID
> 8 3
> 9 3
> 3 2
> 6 2
> 2 5
> 4 1
> 7 1
> 1 5
> 5 5
> Note that 5 is his own boss.
> Getting children of a node is fairly trivial (theres an excellent
> explanation on MSDN and other books), but I'm struggling with getting
> the ancestors
> Given 3, I would like to get all the ancestors of it (in this case 2
> and 5)
> I'm using SQL 2005 and would like to use CTE
>|||Thanks Linchi! Now that I see the solution, I dont know what I was
thinking!!!
Anyways, now I'm onto another problem- I'm trying to combine the
ancestors and the children into a single query. Ideas?
The above example was to simplify the problem at hand, however here is
what I am trying to do with my problem:
WITH PARENTLOCATIONS
AS
(
SELECT LOCID, NAME, PARENTID from LOCREG where locID =
1484056616674400
UNION ALL
SELECT l.Locid, l.name, l.parentid from LOCREG l JOIN ParentLocations
P on l.LOCID = P.ParentID AND l.LOCID <> l.PARENTID
)
Select * from parentlocations
UNION ALL
WITH CHILDLOCATIONS
AS
(
SELECT LOCID, NAME, PARENTID from LOCREG where locID =
1484056616674400
UNION ALL
SELECT l.LocID, l.name, l.parentID from Locreg l JOIN CHILDLOCATIONS c
on c.locid = l.parentid AND l.LOCID <> l.PARENTID
)
Linchi Shea wrote:[vbcol=seagreen]
> declare @.empID int;
> set @.empID = 3;
> with t as (select empID, bossID from emp where empID = @.empID
> union all
> select emp.empID, emp.bossID
> from emp join t on emp.empID = t.bossID
> where emp.empID <> emp.bossID)
> select bossID from t;
> Linchi
> "sprash25@.gmail.com" wrote:
>|||Ok, I got the answer over here:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
"You can, however, define multiple CTEs after the WITH keyword by
separating each CTE with a comma"
WITH CategoryAndNumberOfProducts (CategoryID, CategoryName,
NumberOfProducts) AS
(
SELECT
CategoryID,
CategoryName,
(SELECT COUNT(1) FROM Products p
WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
FROM Categories c
),
ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice)
AS
(
SELECT
ProductID,
CategoryID,
ProductName,
UnitPrice
FROM Products p
WHERE UnitPrice > 10.0
)
SELECT c.CategoryName, c.NumberOfProducts,
p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
INNER JOIN CategoryAndNumberOfProducts c ON
p.CategoryID = c.CategoryID
ORDER BY ProductName
sprash wrote:[vbcol=seagreen]
> Thanks Linchi! Now that I see the solution, I dont know what I was
> thinking!!!
> Anyways, now I'm onto another problem- I'm trying to combine the
> ancestors and the children into a single query. Ideas?
> The above example was to simplify the problem at hand, however here is
> what I am trying to do with my problem:
> WITH PARENTLOCATIONS
> AS
> (
> SELECT LOCID, NAME, PARENTID from LOCREG where locID =
> 1484056616674400
> UNION ALL
> SELECT l.Locid, l.name, l.parentid from LOCREG l JOIN ParentLocations
> P on l.LOCID = P.ParentID AND l.LOCID <> l.PARENTID
> )
> Select * from parentlocations
> UNION ALL
> WITH CHILDLOCATIONS
> AS
> (
> SELECT LOCID, NAME, PARENTID from LOCREG where locID =
> 1484056616674400
> UNION ALL
> SELECT l.LocID, l.name, l.parentID from Locreg l JOIN CHILDLOCATIONS c
> on c.locid = l.parentid AND l.LOCID <> l.PARENTID
> )
>
> Linchi Shea wrote: