[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Parent Child  hierarchy query

Posted on 2011-03-22
4
Medium Priority
?
618 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
  • 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

611 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