parent child - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

parent child

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2008-09-17 : 00:16:48

I need help with this one guys. this is a parent to child relationship
I have samples below. I just want to create a procedure to check if a child key
has relationship to its parent key

tblPermissions 'TABLE
ParentID int NULL
ChildID int

insert into tblpermissions(parentid,childid)
values(null,1)

insert into tblpermissions(parentid,childid)
values(1,2)

insert into tblpermissions(parentid,childid)
values(1,3)

insert into tblpermissions(parentid,childid)
values(2,4)

insert into tblpermissions(parentid,childid)
values(4,5)

isParent(childid,parentid)
isParent(5,4) = YES
isParent(5,3) = No 'because childid(5) inherits from childid(4) which also inherits from childid(2) but childid(2) didnot inherit from childid(3)
isParent(5,1) = YES
isParent(5,2) = YES

TCC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 00:20:04

Have a look at recursive CTE in books online.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-17 : 01:39:16

Hi,

Try with this .May be Helpful to u.

Declare @tblpermissions Table ( Id Int Identity(1,1), parentid Int,childid Int)
insert into @tblpermissions(parentid,childid)
values(null,1)

insert into @tblpermissions(parentid,childid)
values(1,2)

insert into @tblpermissions(parentid,childid)
values(1,3)

insert into @tblpermissions(parentid,childid)
values(2,4)

insert into @tblpermissions(parentid,childid)
values(4,5)

--Select * From @tblpermissions

;WITH Yak (Id, ParentID, childid , [Path], Indent)
AS (
SELECT ID,
ParentID,
childid,
'/' + CAST(ID AS VARCHAR(MAX)),
0
FROM @tblpermissions

UNION ALL

SELECT s.ID,
s.ParentID,
s.childid,
y.Path + '/' + CAST(s.ID AS VARCHAR(MAX)),
y.Indent + 1
FROM Yak AS y
INNER JOIN @tblpermissions AS s ON s.ParentID = y.ID
)

SELECT ParentID,childid, Stuff(path,1,1,'') as path
FROM Yak
ORDER BY [Path]

tishri
Yak Posting Veteran

95 Posts

Posted - 2008-09-17 : 03:46:55

very good. thanks

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources