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 CTEThanks 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:
> 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:
> > 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
> >
> >|||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:
> 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:
> > 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:
> >
> > > 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
> > >
> > >