Solved

SQl  Query to get childs for a parent

Posted on 2011-02-25
12
708 Views
Last Modified: 2012-06-21
I have a table with two cloumns
Parent  and Child

in my case i want to send the parent id so that
i need to return the child of the parent and also all the childs for the child nodes
and each child for each node...


suppose i have this

Parent              Child
1                        3
2                        4
3                         2
4                          9
5                          7

so if i send parent=1
it will return the following

3,2,4,9

Thanks
0
Comment
Question by:Kamal Khaleefa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 100 total points
ID: 34983610
try
WITH CTE(Parent, Child)
AS
(
	SELECT Parent, Child
	FROM Table1 A
	WHERE PARENT = 1
	UNION ALL
	SELECT Parent, Child
	FROM Table1 A
	INNER JOIN CTE IH ON IH.Child = A.Parent
)
SELECT Parent, Child FROM CTE

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34983822
check this.
declare @Parent int = 1
;WITH cte AS (
	SELECT Child,  Parent
	FROM your_table -- replace with your table name
	WHERE Parent = @Parent
	UNION ALL
	SELECT t.Child, t.Parent
	FROM your_table t
	INNER JOIN cte c ON t.Parent = c.Child) 
SELECT Parent,Child FROM cte 
 /*
 Parent	Child
1	3
3	2
2	4
4	9
 */

Open in new window

0
 
LVL 26

Expert Comment

by:tigin44
ID: 34983829
WITH childRelation (Parent, Child)
AS
(
      SELECT Parent, Child
      FROM yourTable
      WHERE Parent= 1
      UNION ALL
      SELECT Y.Parent, Y.Child
      FROM yourTable Y
            INNER JOIN childRelation C ON Y.Parent = C.Child
)

SELECT Child
FROM childRelation
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 16

Author Comment

by:Kamal Khaleefa
ID: 34983932
@ewangoya:
Thanks for ur comment
There is one problem
suppose i have this case
Parent   child
1            2
2            3
3           4
4          5
5          6
6          3
in this case if i send  1 as a perent
then it will be infinity loop
because if u see
parent 6 has 3 as a child
so it will repeat it sels infinity
how i can overcome this
7      0
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34983989
Is that a valid scenario? Does a child node have two parent nodes?
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34984093

This should never occur unless your data is invalid,
You can try and limit the level of data to show

;WITH CTE(Parent, Child, Level)
AS
(
      SELECT Parent, Child, 0
      FROM @t A
      WHERE PARENT = 1
      UNION ALL
      SELECT A.Parent, A.Child, IH.Level+1
      FROM @t A
      INNER JOIN CTE IH ON IH.Child = A.Parent
      where IH.Level < 10
)
SELECT Parent, Child FROM CTE

0
 
LVL 16

Author Comment

by:Kamal Khaleefa
ID: 34984115
@Sharath_123:
unfortunately i have this scenario
 my table is for main Company and sub company

so i have a scenario that a company is taking contracts from two other companies
so in the relation it will look that it has two parents
0
 
LVL 16

Author Comment

by:Kamal Khaleefa
ID: 34984179
Dear @ewangoya:
in ur code above, u r only taking the top 10 results
i think the code should be modified,so that there will be a condition like if the result is already exist we should dont do it again

for me i got the following error
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34985928
refer this URL: http://www.dbforums.com/microsoft-sql-server/1642466-multiple-parents-tree-digraph-implementation.html

used the same logic for your requirement.
declare @table table(Parent int,Child int)
insert @table values (1,2),(2,3),(3,4),(4,5),(5,6),(6,3)
--insert @table values (1,3),(2,4),(3,2),(4,9),(5,7)
--insert @table values (1,2),(2,3),(3,1)
declare @Ids table(Parent int)
;with Parent as (select Parent from @table),
      Child as (select Child from @table)
insert @Ids
select distinct p.Parent
  from Parent p
  left join Child c on p.Parent = c.Child
 where c.Child is null
 union
select top 1 Parent from Parent
;with CTE as (
select Parent,Child,
       CONVERT(varchar(max),Parent) Depth,
       Parent TopParentID 
  from @table
 union all
select t.Parent,t.Child,
       c.Depth + ', ' + convert(varchar,t.Parent),
       c.TopParentID TopParentID
  from CTE c
  join @table t on t.Parent = c.Child
 where c.Depth not like '%' + convert(varchar,t.Parent) + '%'
)
select distinct Child 
  from CTE
 where TopParentID = 1
/*
Child
2
3
4
5
6
*/

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 34985936
You can add one more filter condition: TopParentID <> Child
declare @table table(Parent int,Child int)
insert @table values (1,2),(2,3),(3,4),(4,5),(5,6),(6,3)
--insert @table values (1,3),(2,4),(3,2),(4,9),(5,7)
--insert @table values (1,2),(2,3),(3,1)
declare @Ids table(Parent int)
;with Parent as (select Parent from @table),
      Child as (select Child from @table)
insert @Ids
select distinct p.Parent
  from Parent p
  left join Child c on p.Parent = c.Child
 where c.Child is null
 union
select top 1 Parent from Parent
;with CTE as (
select Parent,Child,
       CONVERT(varchar(max),Parent) Depth,
       Parent TopParentID 
  from @table
 union all
select t.Parent,t.Child,
       c.Depth + ', ' + convert(varchar,t.Parent),
       c.TopParentID TopParentID
  from CTE c
  join @table t on t.Parent = c.Child
 where c.Depth not like '%' + convert(varchar,t.Parent) + '%'
)
select distinct Child
  from CTE
 where TopParentID <> Child and TopParentID = 3
/*
Child
4
5
6
*/

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 200 total points
ID: 34986267
;WITH CTE AS (
select Parent,Child,path= '\\' + convert(varchar(max),parent)
                        + '\' + convert(varchar(max),child)
                        + '\'
from tbl
where parent = 1
union all
select A.Parent, A.Child, Path + convert(varchar(max),A.child) + '\'
from cte
inner join tbl A on A.Parent = cte.child
where Path not like '%\' + convert(varchar(max),A.child) + '\%'
)
select * from cte

Open in new window


Bonus - This code will even show you the path to a particular child from the root.
0
 
LVL 16

Author Closing Comment

by:Kamal Khaleefa
ID: 34987339
Thank you all
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query help 3 24
Connect Gridview column to Textbox in C# 2 39
Applying Roles in Common Scenarios 3 17
Error in sql query statment. 21 44
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question