retrive child and sub child from table [modified]
-
sorry buddys it might be in wrong category, but i use ASP.NET platform for devloping so... now my question: i have table formated as three column: id,parent_id,name ex.. id /parent_id /name 1 /null /ABC1 2 /1 /ABC2 3 /1 /ABC3 4 /2 /ABC4 5 /4 /ABC5 6 /3 /ABC6 for example i wnt to retrive perticulars child and sub child of id 2 then it should give result: 2 /1 /ABC2 4 /2 /ABC4 5 /4 /ABC5 actually i want perticulars child and sub childs.. if id is 2 den i want child of id 2, and sub childs of id 2,s child and so on.. Please giv me appropriate query for it... thanks in advance
modified on Wednesday, August 19, 2009 2:58 AM
-
sorry buddys it might be in wrong category, but i use ASP.NET platform for devloping so... now my question: i have table formated as three column: id,parent_id,name ex.. id /parent_id /name 1 /null /ABC1 2 /1 /ABC2 3 /1 /ABC3 4 /2 /ABC4 5 /4 /ABC5 6 /3 /ABC6 for example i wnt to retrive perticulars child and sub child of id 2 then it should give result: 2 /1 /ABC2 4 /2 /ABC4 5 /4 /ABC5 actually i want perticulars child and sub childs.. if id is 2 den i want child of id 2, and sub childs of id 2,s child and so on.. Please giv me appropriate query for it... thanks in advance
modified on Wednesday, August 19, 2009 2:58 AM
-
sorry buddys it might be in wrong category, but i use ASP.NET platform for devloping so... now my question: i have table formated as three column: id,parent_id,name ex.. id /parent_id /name 1 /null /ABC1 2 /1 /ABC2 3 /1 /ABC3 4 /2 /ABC4 5 /4 /ABC5 6 /3 /ABC6 for example i wnt to retrive perticulars child and sub child of id 2 then it should give result: 2 /1 /ABC2 4 /2 /ABC4 5 /4 /ABC5 actually i want perticulars child and sub childs.. if id is 2 den i want child of id 2, and sub childs of id 2,s child and so on.. Please giv me appropriate query for it... thanks in advance
modified on Wednesday, August 19, 2009 2:58 AM
sorry, this is wrong forum... if you post you question in a appropriate forum you will be clearly guided by our experts.....
Padmanabhan My Articles: Articles[^] My latest Article: Word Automation[^]
-
actually i want perticulars child and sub childs.. if id is 2 den i want child of id 2, and sub childs of id 2,s child and so on..
-
sorry buddys it might be in wrong category, but i use ASP.NET platform for devloping so... now my question: i have table formated as three column: id,parent_id,name ex.. id /parent_id /name 1 /null /ABC1 2 /1 /ABC2 3 /1 /ABC3 4 /2 /ABC4 5 /4 /ABC5 6 /3 /ABC6 for example i wnt to retrive perticulars child and sub child of id 2 then it should give result: 2 /1 /ABC2 4 /2 /ABC4 5 /4 /ABC5 actually i want perticulars child and sub childs.. if id is 2 den i want child of id 2, and sub childs of id 2,s child and so on.. Please giv me appropriate query for it... thanks in advance
modified on Wednesday, August 19, 2009 2:58 AM
Since you asked about query, here is a possible implementation using stored procedure. 1 - Write a stored procedure that takes a parent id. 2 - Do a select to get all the children of the supplied parent id and assign it to a T-SQL cursor. 3 - Iterate over all of the children. 3.1 - Add row into a temporary table 3.2 - Call this procedure again (recursive) by passing the child id as parent id. Goes to step 2 4 - Select all the rows from the temporary table if
@@NESTLEVELis 1. 5 - Drop temporary table, de-allocate cursor. Note: SQL server has a stored procedure nesting limit. I guess it is 32. So if you have an item which is nested more than 32 levels, you will get error. In such case, you need to handle this in the application code rather than on a procedure. Also you need to make sure that the temporary table is getting created only once, that is when the procedure starts executing for the first time. All the nested calls will get the temporary table created on the first call. This can be done in a better way using C# (or whatever language you use).Navaneeth How to use google | Ask smart questions
-
sorry buddys it might be in wrong category, but i use ASP.NET platform for devloping so... now my question: i have table formated as three column: id,parent_id,name ex.. id /parent_id /name 1 /null /ABC1 2 /1 /ABC2 3 /1 /ABC3 4 /2 /ABC4 5 /4 /ABC5 6 /3 /ABC6 for example i wnt to retrive perticulars child and sub child of id 2 then it should give result: 2 /1 /ABC2 4 /2 /ABC4 5 /4 /ABC5 actually i want perticulars child and sub childs.. if id is 2 den i want child of id 2, and sub childs of id 2,s child and so on.. Please giv me appropriate query for it... thanks in advance
modified on Wednesday, August 19, 2009 2:58 AM
you mean you need hierarchical data. If particular node is chosen, you need all its child elements. I think it is very easy to do in Recursion, but very tough to do it in a single SQL. Try it, It needs Self joins in query. I will do this when get time and let you know. :thumbsup:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
-
you mean you need hierarchical data. If particular node is chosen, you need all its child elements. I think it is very easy to do in Recursion, but very tough to do it in a single SQL. Try it, It needs Self joins in query. I will do this when get time and let you know. :thumbsup:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
ok.. waitin for ur reply.. thanks..
-
Since you asked about query, here is a possible implementation using stored procedure. 1 - Write a stored procedure that takes a parent id. 2 - Do a select to get all the children of the supplied parent id and assign it to a T-SQL cursor. 3 - Iterate over all of the children. 3.1 - Add row into a temporary table 3.2 - Call this procedure again (recursive) by passing the child id as parent id. Goes to step 2 4 - Select all the rows from the temporary table if
@@NESTLEVELis 1. 5 - Drop temporary table, de-allocate cursor. Note: SQL server has a stored procedure nesting limit. I guess it is 32. So if you have an item which is nested more than 32 levels, you will get error. In such case, you need to handle this in the application code rather than on a procedure. Also you need to make sure that the temporary table is getting created only once, that is when the procedure starts executing for the first time. All the nested calls will get the temporary table created on the first call. This can be done in a better way using C# (or whatever language you use).Navaneeth How to use google | Ask smart questions
ok... i understand ur method, but i faced problem to create procedure.. can you reply me demo procedure??? thanks..
-
ok... i understand ur method, but i faced problem to create procedure.. can you reply me demo procedure??? thanks..
punit_belani wrote:
but i faced problem to create procedure
What problem are you facing in creating a procedure?
punit_belani wrote:
can you reply me demo procedure
Its very easy to create your own procedure with the steps provided in my last post. If you have problems, come back with specific error messages. Do a search on creating nested stored procedures and learn to do it yourself. Spoon feeding will not benefit you.
Navaneeth How to use google | Ask smart questions