SQL query to return child-paren relationship

Question

Post reply

SQL query to return child-paren relationship

piyush_srivastava

SSC Rookie

Points: 32
More actions
December 6, 2011 at 11:58 pm

#247899

I have below table with fields (Plan_id, child_id, Child_type)
Please suggest me the query which return the maximum path of the child parent relationshiop, please refer below conditions.
Plan Id Child Id Child Type
TP1 TC1 C
TP1 TP2 P
TP1 TP3 P
TP2 TC2 C
TP2 TP4 P
TP3 TC3 C
TP4 TC4 C
Now If I pass you PlanId as TP1 then..
1. TP1 has 3 child.. TC1, TP2, TP3
2. Leave aside TC.
3. TP2 has 2 child TC2 and TP4
4. TP3 has 1 child TC3
5. TP4 has 1 child TC4
So now.. TP1 has hierarchy as ….
1. TP1 -> TP2 -> TP4 -> TC4 so its level is 3.
2. TP1 -> TP3 -> TC3 so its level is 2.
3. TP1 -> TC1 so its level is 1.
Max level is 3 .. so you need to return me this value 3.
Thanks!
Piyush

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Sounds like a homework question,

have a look at hierarchyId or a recursive cte.

Clear Sky SQL
My Blog[/url]

piyush_srivastava SSC Rookie Points: 32 More actions · Answer 2

I am not aware of recursive cte, please help me by providing the query which can solve my above problem.

I tried lot but coudn't get through.

Regards!

Piyush

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

piyush_srivastava (12/7/2011)
I am not aware of recursive cte, please help me by providing the query which can solve my above problem.
I tried lot but coudn't get through.
Regards!
Piyush

Recursive Queries Using Common Table Expressions

http://msdn.microsoft.com/en-us/library/ms186243.aspx

piyush_srivastava SSC Rookie Points: 32 More actions · Answer 4

It seems we need to hard code the number of level of recursion.

But we don't know what is the level of hierarchy.

Please help

subbu1 Hall of Fame Points: 3281 More actions · Answer 5

declare @table table(immid varchar(10),ppid varchar(10))

insert into @table values('TP1','TC1')

insert into @table values('TP1','TP2')

insert into @table values('TP1','TP3')

insert into @table values('TP2','TC2')

insert into @table values('TP2','TP4')

insert into @table values('TP3','TC3')

insert into @table values('TP4','TC4')

select * from @table;

with main(Mgrid,id,hierarchy1,level)

as

(

select immid,ppid,convert(varchar(200),ppid) as hierarchy1,1 as leval from @table t

where t.immid=t.ppid or t.immid<>'' OR t.immid is null

union all

select h.Mgrid,k.ppid,convert(varchar(200),h.hierarchy1+'->'+k.ppid),h.level+1

from @table k

inner join main h

on h.id=k.immid)

select distinct MAX(level) from main

Bipinkumar Yadav SSC-Addicted Points: 443 More actions · Answer 6

try This.

DECLARE @CIDHR VARCHAR(100)

DECLARE @HARAR TABLE(PID VARCHAR(10),CID VARCHAR(10),CTP VARCHAR(10))

SET @CIDHR=''

INSERT INTO @HARAR

SELECT 'TP1' as PID,'TC1' as CID,'C' as CTP UNION ALL

SELECT 'TP1' as PID,'TP2' as CID,'P' as CTP UNION ALL

SELECT 'TP1' as PID,'TP3' as CID,'P' as CTP UNION ALL

SELECT 'TP2' as PID,'TC2' as CID,'C' as CTP UNION ALL

SELECT 'TP2' as PID,'TP4' as CID,'P' as CTP UNION ALL

SELECT 'TP3' as PID,'TC3' as CID,'C' as CTP UNION ALL

SELECT 'TP4' as PID,'TC4' as CID,'C' as CTP

SELECt @CIDHR=@CIDHR +'->'+ CID from @HARAR where PID='TP2'---Create USER DEFINE FUNCTION OR USE AS IT IS

SELECt DISTINCT PID,@CIDHR AS CHHR FROM @HARAR where PID='TP2'

Erwin Dockx SSC Eights! Points: 852 More actions · Answer 7

piyush_srivastava (12/7/2011)
I am not aware of recursive cte, please help me by providing the query which can solve my above problem.
I tried lot but coudn't get through.
Regards!
Piyush

I put together a small demo which show what's possible with a CTE, hope it helps:

declare @demo table (PlanId char(3), ChildId char(3), ChildType char(1));

insert into @demo([PlanId], [ChildId], [ChildType])

values ('TP1', 'TC1', 'C')

, ('TP1', 'TP2', 'P')

, ('TP1', 'TP3', 'P')

, ('TP2', 'TC2', 'C')

, ('TP2', 'TP4', 'P')

, ('TP3', 'TC3', 'C')

, ('TP4', 'TC4', 'C')

;

select * from @demo;

with cteDemo as

(

select [d].[PlanId]

, [d].[ChildId]

, [d].[ChildType]

, 0 as [PlanLevel]

, 1 as [ChildLevel]

, convert(varchar(100), [d].[PlanId] + ' -> ' + [d].[ChildId]) as [Path]

from @demo as d

where [d].[PlanId] not in (select [sd].[ChildId] from @demo as sd)

union all

select [cd].[PlanId]

, [cd].[ChildId]

, [cd].[ChildType]

, [pd].[PlanLevel] + 1

, [pd].[ChildLevel] + 1

, convert(varchar(100), [pd].[Path] + ' -> ' + [cd].[ChildId])

from @demo as cd

inner join [cteDemo] as pd on [cd].[PlanId] = [pd].[ChildId]

)

select *

from cteDemo;

_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.

You can find me on LinkedIn.
I support The Programmer's Bill of Rights.

MCITP, MCDBA, MCSD