Solved

Parent Child  hierarchy query

Posted on 2011-03-22
4
600 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:alokvaria
[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
  • 2
4 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 35191394
what is the structure of the table that you want to traverse...
0
 
LVL 3

Author Comment

by:alokvaria
ID: 35191574
I got my own answer sorry for trouble.
Thanks,
Alok Varia
0
 
LVL 3

Accepted Solution

by:
kumarnimavat earned 500 total points
ID: 35191723
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
 
LVL 3

Author Closing Comment

by:alokvaria
ID: 35191932
Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

626 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