Solved

SQl  Query to get childs for a parent

Posted on 2011-02-25
12
747 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
Technology Partners: 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!

 
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

628 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