Parent Child hierarchy query

Hi,
I want a query which gives me all the child records for a parent row. I don't know how deep the child record is. Also i have not access to database. Is there a way to get it?
And also is there a way to go from down to up. Eg if i select one child I want hierarchy upto the parent.
Thanks in advance
LVL 3
alokvariaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kumarnimavatConnect With a Mentor Commented:
You may use CTE ie, Common Table Expression in sql server. We may use CTE to fetch N-level hierarchy from Root to the last child node.

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

This is a function that i have used in my project to find all the childs using CTE

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:            <Author,Khyati>
-- Create date: <Create Date,29May2008,>
-- Description:      <Description, Select Whole Child Tree accordingly Level,>
-- Execute: Select * from ufn_SelectChilds(null,175,null)
-- =============================================
ALTER FUNCTION [dbo].[ufn_SelectChilds]
(
      @UserId int,
      @UserRoleRelationshipId int,
      @Level int
)
RETURNS TABLE
AS
RETURN
(
      WITH Childs
      (
            UserRoleRelationshipsId,
            ChildUserId,
            ChildUserName,
            ChildRoleId,
            ChildRoleName,
            CommissionAgrDoc,
            BrandingId,
            CompanyTitle,
            CommissionRuleId,
            CommissionRuleName,
            StatusId,
            StatusName,
            ParentUserId,
            ParentUserName,      
            ParentRoleId,
            ParentRoleName,
            ParentRelationshipId,
            Level
      )
      AS
      (
            SELECT
                  URR.UserRoleRelationshipsId,
                  URR.ChildId 'ChildUserId',
                  URR.UserName 'ChildUserName',
                  URR.ChildRoleId 'ChildRoleId',
                  URR.RoleName 'ChildRoleName',
                  URR.CommissionAgrDoc,
                  URR.BrandingId,
                  URR.CompanyTitle,
                  URR.CommissionRuleId,
                  URR.CommissionRuleName,
                  URR.StatusId,
                  URR.StatusName,
                  URR2.ChildId 'ParentUserId',
                  URR2.UserName 'ParentUserName',      
                  URR2.ChildRoleId 'ParentRoleId',
                  URR2.RoleName 'ParentRoleName',
                  URR.ParentRelationshipId,
                  0 AS Level
            FROM uv_UserRoleRelationships AS URR
                  LEFT OUTER JOIN
                  (
                        SELECT iURR.ChildId, iU.UserName, iURR.ChildRoleId, iR.RoleName, iURR.UserRoleRelationshipsId
                        FROM uv_UserRoleRelationships iURR
                        LEFT OUTER JOIN Roles iR ON iURR.ChildRoleId = iR.RoleId
                        LEFT OUTER JOIN Users iU ON iURR.ChildId = iU.UserId
                  )AS URR2 ON URR2.UserRoleRelationshipsId = URR.ParentRelationshipId
            WHERE URR.ChildId = coalesce(@UserId, URR.ChildId)
            AND URR.UserRoleRelationshipsId = coalesce(@UserRoleRelationshipId, URR.UserRoleRelationshipsId)
            UNION ALL
            SELECT
                  URR.UserRoleRelationshipsId,
                  URR.ChildId 'ChildUserId',
                  URR.UserName 'ChildUserName',
                  URR.ChildRoleId 'ChildRoleId',
                  URR.RoleName 'ChildRoleName',
                  URR.CommissionAgrDoc,
                  URR.BrandingId,
                  URR.CompanyTitle,
                  URR.CommissionRuleId,
                  URR.CommissionRuleName,
                  URR.StatusId,
                  URR.StatusName,
                  c.ChildUserId 'ParentUserId',
                  c.ChildUserName 'ParentUserName',      
                  c.ChildRoleId 'MyRoleId',
                  c.ChildRoleName 'MyRoleName',
                  URR.ParentRelationshipId,
                  Level + 1
            FROM uv_UserRoleRelationships AS URR
            INNER JOIN Childs as c
            on URR.ParentRelationshipId = c.UserRoleRelationshipsId
      )
      SELECT
            UserRoleRelationshipsId,
            ChildUserId,
            ChildUserName,
            ChildRoleId,
            ChildRoleName,
            CommissionAgrDoc,
            BrandingId,
            CompanyTitle,
            CommissionRuleId,
            CommissionRuleName,
            StatusId,
            StatusName,
            ParentUserId,
            ParentUserName,      
            ParentRoleId,
            ParentRoleName,
            ParentRelationshipId,
            Level
      FROM Childs
      WHERE Level = coalesce(@Level, Childs.Level)
)

0
 
tigin44Commented:
what is the structure of the table that you want to traverse...
0
 
alokvariaAuthor Commented:
I got my own answer sorry for trouble.
Thanks,
Alok Varia
0
 
alokvariaAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.