Solved

Parent Child  hierarchy query

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause in stored procedure 8 50
Move SQL 2005 Express to Server 2012R2 19 104
Need help with a query 6 67
Strange msg in the SSMS pane 13 49
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.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now