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:
Showing posts with label thats. Show all posts
Showing posts with label thats. Show all posts
Monday, March 26, 2012
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 CTE
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
>
|||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:
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
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
>
|||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:
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
> > >
> > >
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
> > >
> > >
Monday, March 19, 2012
Find table/indexes size
Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.
look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.
|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:
> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>
|||Thank you all! Just what I needed!
FireStarter
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.
look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.
|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:
> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>
|||Thank you all! Just what I needed!
FireStarter
Friday, March 9, 2012
find Minimum in range thats not in Table?
Hi Folks,
any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.
Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so
I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.
thanks in advance!could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy|||Originally posted by r937
could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy
Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.
'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.
I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.
thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name
if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:
-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match
each of these requires some way of specifying the set of things that aren't there
you don't actually have to have an auxiliary table, though
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy
darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(
thanks,
zaphod|||yeah, but i didn't know i was right at the time!
meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype
rudy
any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.
Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so
I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.
thanks in advance!could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy|||Originally posted by r937
could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy
Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.
'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.
I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.
thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name
if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:
-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match
each of these requires some way of specifying the set of things that aren't there
you don't actually have to have an auxiliary table, though
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy
darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(
thanks,
zaphod|||yeah, but i didn't know i was right at the time!
meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype
rudy
Sunday, February 19, 2012
Filtering through Matrix or groups in matrix
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
Filter work fine with "=" sign so what u need to do is
fileter condition < =6 (In expression use =6 instead of plain 6)
or you may try using =cint(6)
Subscribe to:
Posts (Atom)