CODEWITHSUNDEEP
sqlsql-serverjoin
FaheemMCFC
FaheemMCFC

Reputation: 21

How to get child of master entry from sql table

I have a table for ledgers. It consists of MasterID as unique ID and a parentID for joining.

Model of my table

enter image description here

As in the image my parent ledger is 'A'. 'B' is direct child of 'A'. 'C' is direct child of 'B' and 'D' is direct child of 'C'. I want a select query to select all childs of 'A'. i.e, result will be B,C,D. I am beginner in SQL. I tried some while loop for this, but only direct child was accessible. I am not able make a logic for the requirement. Thank you in advance.

Upvotes: 0

Views: 242

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Here is an option that uses HierarchyID

The range keys (R1 / R2) in the final select are optional.

Example

Declare @YourTable table (MasterID  int ,name varchar(50), ParentID int);
Insert Into @YourTable values 
( 1, 'A', null),
( 2, 'B', 1),
( 3, 'C', 2),
( 4, 'D', 3)

Declare @Top   int         = 2           --<<  Sets top of Hier Try NULL for entire hier
Declare @Nest  varchar(25) = '|-----'    --<<  Optional: Added for readability

;with cteP as (
      Select MasterID
            ,ParentID 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',MasterID,'/'))
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ParentID ,-1) else MasterID end
      Union  All
      Select MasterID  = r.MasterID
            ,ParentID  = r.ParentID 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.MasterID,'/'))
      From   @YourTable r
      Join   cteP p on r.ParentID  = p.MasterID)
Select R1    = Row_Number() over (Order By HierID)
      ,R2    = (Select count(*) From cteP where HierID.ToString() like A.HierID.ToString()+'%')
      ,Lvl   = HierID.GetLevel()
      ,MasterID
      ,ParentID
      ,Name  = Replicate(@Nest,HierID.GetLevel()-1) + Name
      ,HierID
      ,HierID_String = HierID.ToString()
 From cteP A
 Order By A.HierID

Returns

enter image description here

Upvotes: 0

GMB
GMB

Reputation: 222482

That'as a typical hierarchical query. Consider the solution that makes use of a recursive cte:

with cte as (
    select masterID rootID, masterID, name, parentid
    from mytable    
    where parentID is null
    union all
    select c.rootID, t.masterID, t.name, t.parentID
    from mytable t
    inner join cte c on c.masterID = t.parentID 
)
select * from cte

As commented by The Impaler, you can change starting condition where parentID is null to the id of another node if needed.

With your sample data, this yields:

rootID | masterID | name | parentid
-----: | -------: | :--- | -------:
     1 |        1 | A    |     null
     1 |        2 | B    |        1
     1 |        3 | C    |        2
     1 |        4 | D    |        3

Note that I kept track of the id of the root object, so it is easier to understand what is going on if there are several roots in your data.

You can also use the root to generate a flat list of children:

with cte as (
    select masterID rootID, masterID, name, parentid
    from mytable    
    where parentID is null
    union all
    select c.rootID, t.masterID, t.name, t.parentID
    from mytable t
    inner join cte c on c.masterID = t.parentID 
)
select rootID, string_agg(masterID, ',') childrenID from cte group by rootID

rootID | childrenID
-----: | :---------
     1 | 1,2,3,4

Demo on DB Fiddle

Upvotes: 1

Related Questions