Solved

Parent Child  hierarchy query

Posted on 2011-03-22
4
592 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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query to summarize items per month 5 85
SQL Query with Sum and Detail rows 2 68
Applying Roles in Common Scenarios 3 43
Better way to filter date  - Query 5 45
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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