June 24, 2008 at 11:18 pm
Hello all
I have a table where data is like department, sub department , sub sub department. Table structure is like a tree of departments e.g.
department Id, Department name, Parent Id
1, HR, 0
2, Finance, 0
3, Production, 0
4, Enterprise, 3
5, Mobile, 3
6, Telecom, 3
so Enterprise, mobile and telecom are children of Production. however HR, Finance and Production are root nodes.
My question is If I want to write a query for getting all children of Production upto the available depth i.e. Production, its childes, its childs childs if depth is 2.
what is the best way to handle this in SQL 2005.
Thanks & Regards
Rashmi
June 25, 2008 at 2:00 am
Probably a recursive CTE.
Read Books Online how to write these kind of queries.
N 56°04'39.16"
E 12°55'05.25"
June 25, 2008 at 4:16 am
Hi Rashmi,
Please try the below. Please also note that it doesn't do exactly what you want, it will only return ALL of the children of a specified node. I ran out of time and I'm super hungry!!
IF OBJECT_ID('tempdb.dbo.#department') IS NOT NULL
DROP TABLE #department
GO
IF OBJECT_ID('tempdb.dbo.#department2') IS NOT NULL
DROP TABLE #department2
GO
CREATE TABLE #department
(
department_id INTEGER,
department_name VARCHAR(40),
parent_id INTEGER
)
CREATE TABLE #department2
(
department_id2 INTEGER,
department_name2 VARCHAR(40),
parent_id2 INTEGER
)
INSERT INTO #department (department_id,department_name,parent_id)
SELECT 1,'HR',0 UNION ALL
SELECT 2,'Finance',0 UNION ALL
SELECT 3,'Production',0 UNION ALL
SELECT 4,'Enterprise',3 UNION ALL
SELECT 5,'Mobile',3 UNION ALL
SELECT 6,'Telecom',3 UNION ALL
SELECT 7,'Towers',5 UNION ALL
SELECT 8,'Cables',5 UNION ALL
SELECT 9,'Multiplexers',8 UNION ALL
SELECT 10,'Pair Gain',8
IF OBJECT_ID('getChildren') IS NOT NULL
DROP PROCEDURE getChildren
GO
CREATE PROCEDURE getChildren (@depth INTEGER, @department_id INTEGER) AS
BEGIN
DECLARE @node_id INTEGER
IF @depth = 1 --if we're at the top of the tree insert the parent along with it's first set of children
BEGIN
INSERT INTO #department2 SELECT * FROM #department WHERE department_id= @department_id
INSERT INTO #department2 SELECT * FROM #department WHERE parent_id= @department_id
SELECT @depth = @depth + 1
END
ELSE
BEGIN -- if we're not at the start don't insert the parent because it's already in the table. Only insert the children
INSERT INTO #department2 SELECT * FROM #department WHERE parent_id= @department_id
SELECT @depth = @depth + 1
END
DECLARE csrChildren CURSOR LOCAL FOR -- create a cursor for the children of the current node
SELECT department_id2 FROM #department2 WHERE parent_id2 = @department_id
OPEN csrChildren
FETCH NEXT FROM csrChildren INTO @node_id
WHILE @@FETCH_STATUS <> -1
BEGIN
EXEC getChildren 2, @node_id --recurse to find the children of the children
FETCH NEXT FROM csrChildren INTO @node_id
END
CLOSE csrChildren
END
SELECT * FROM #department2 -- return all the children
delete from #department2 -- you'll need to make sure you delete from the table when you're testing
exec getChildren 1, 3 --at the moment the 1 needs to be a constant, but the 3 can be any node you want to start at.
Cheers,
Jim.
June 25, 2008 at 6:49 am
DECLARE @T Table (DeptID int,DeptName varchar(50), ParentID int)
DECLARE @DeptID int
SET @DeptID = 3
INSERT INTO @T
SELECT 1, 'HR', 0 UNION
SELECT 2, 'Finance', 0 UNION
SELECT 3, 'Production', 0 UNION
SELECT 4, 'Enterprise', 3 UNION
SELECT 5, 'Mobile', 3 UNION
SELECT 6, 'Telecom', 3 UNION
SELECT 7, 'TelecomChild1', 6 UNION
SELECT 8, 'TelecomChild2', 6 UNION
SELECT 9, 'TelecomChild3', 6;
--- CTE ---
WITH MyCTE (DeptID,DeptName,ParentID)
AS
(
SELECT DeptID,DeptName,ParentID FROM @T WHERE DeptID = @DeptID
UNION ALL
SELECT A.DeptID,A.DeptName,A.ParentID FROM @T A
INNER JOIN MyCTE B ON B.DeptID = A.ParentID
)
SELECT * FROM MyCTE
June 25, 2008 at 6:54 am
Jim, you believe that is easier than a recursive CTE?
Hari, where is the hierarchy?
DECLARE@Sample TABLE (NodeID INT, NodeName VARCHAR(20), ParentNodeID INT)
INSERT@Sample
SELECT 1,'HR',0 UNION ALL
SELECT 2,'Finance',0 UNION ALL
SELECT 3,'Production',0 UNION ALL
SELECT 4,'Enterprise',3 UNION ALL
SELECT 5,'Mobile',3 UNION ALL
SELECT 6,'Telecom',3 UNION ALL
SELECT 7,'Towers',5 UNION ALL
SELECT 8,'Cables',5 UNION ALL
SELECT 9,'Multiplexers',8 UNION ALL
SELECT 10,'Pair Gain',8
;WITH Yak (NodeID, NodeName, ParentNodeID, NodePath, Indent)
AS (
SELECTNodeID,
NodeName,
ParentNodeID,
'/' + CAST(NodeID AS VARCHAR(MAX)),
0
FROM@Sample
WHEREParentNodeID = 0
UNION ALL
SELECTs.NodeID,
s.NodeName,
s.ParentNodeID,
y.NodePath + '/' + CAST(s.NodeID AS VARCHAR(12)),
y.Indent + 1
FROM@Sample AS s
INNER JOINYak AS y ON y.NodeID = s.ParentNodeID
)
SELECTNodeID,
LEFT(REPLICATE(' ', Indent) + NodeName, 24) AS NodeName
FROMYak
ORDER BYNodePath
N 56°04'39.16"
E 12°55'05.25"
June 25, 2008 at 5:03 pm
June 25, 2008 at 5:41 pm
Rashmi,
How often is the hierarchy going to change?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2008 at 11:49 am
If the hierarchy is relatively static (doesn't change often), you might want to look up "nested sets" online. It does hierarchies MUCH faster than CTEs or other methods. Just Google/Yahoo/Live/whatever "nested sets hierarchy" and you'll find what you're looking for. Best articles on it are by Joe Celko.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 26, 2008 at 5:24 pm
GSquared (6/26/2008)
If the hierarchy is relatively static (doesn't change often), you might want to look up "nested sets" online. It does hierarchies MUCH faster than CTEs or other methods. Just Google/Yahoo/Live/whatever "nested sets hierarchy" and you'll find what you're looking for. Best articles on it are by Joe Celko.
That's exactly where I was going with that...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2008 at 3:32 am
hey all thanks for your replies.
I tried to use CTE here but how to specify a specific depth in CTE query. In a heirarchy of depth 6 if i want all childs upto depth 2 how to do that?
hey what is nested table hierarchy.. can you guys help me in finding out.
Regards
Rashmi
July 3, 2008 at 5:46 am
rashmi.todkar (7/3/2008)
hey what is nested table hierarchy.. can you guys help me in finding out.
This link explains...
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2008 at 5:48 am
rashmi.todkar (7/3/2008)
hey all thanks for your replies.
By the way... you still owe us some replies so we can help... like my question about "How often will the data in the hierarchy change?"
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2008 at 11:58 am
rashmi.todkar (7/3/2008)
hey all thanks for your replies.I tried to use CTE here but how to specify a specific depth in CTE query. In a heirarchy of depth 6 if i want all childs upto depth 2 how to do that?
hey what is nested table hierarchy.. can you guys help me in finding out.
Regards
Rashmi
On the depth, you can add a Depth column to the CTE. In the top query, assign it a value of 0 or 1 (depending on which works for you), and in the query after the Union All statement, have it as +1 from the parent data.
Like this:
;with HierarchyCTE (NodeID, ParentID, Depth) as
(select NodeID, ParentID, 0
from dbo.HierarchyTable
where NodeID = @InputParameter
Union All
select H2.NodeID, H2.ParentID, Depth+1
from dbo.HierarchyTable H2
inner join HierarchyCTE
on H2.ParentID = HierarchyCTE.NodeID
where Depth <= 1)
select NodeID, ParentID
from HierarchyCTE
That will get you up to Depth 2 (two levels of children, because I started with 0).
Jeff gave you a good link to nested sets data. Simple summary: You assign a range of numbers to a parent, and each child has a range that's inside the range for the parent.
For example:
Parent has 1 through 10
Child1 has 1-5, Child2 has 6-10
Grandchild1 has 1-1, Grandchild2 has 2-2
Querying this is very simple, and blindingly fast if it's indexed correctly. Substitute departments for children, or bill-of-materials components, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 9:07 pm
... and we STILL need to know how often the data in the hierarchy will change... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2008 at 1:13 am
Thanks all.
I gone through nested sets options its really best one for high performance reads on such data. but update and insert is bit costly however I can afford it for some of my tables.
Let me try CTE option I will get back to you once i finalize the solution.
Once again thanks guys 🙂
regards
Rashmi
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply