Solved

SQl  Query to get childs for a parent

Posted on 2011-02-25
12
670 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
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 100 total points
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
 
LVL 16

Author Comment

by:Kamal Khaleefa
Comment Utility
@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 40

Expert Comment

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

Expert Comment

by:ewangoya
Comment Utility

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Author Comment

by:Kamal Khaleefa
Comment Utility
@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
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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 40

Assisted Solution

by:Sharath
Sharath earned 200 total points
Comment Utility
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
Comment Utility
;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
Comment Utility
Thank you all
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now