Write a SQL query to get the only Super Child records from the Table

Question

I am struggling with preparing one SQL query that should return the expected data in one shot.

My requirement is to get the data from SQl table called JobCollection in such a way that which will return a data as highlighted in green border in below image.

This data is organised in Parent Child way. As you see below,

• JCId 1 is a ParentID of JCId 3,4,5. Same as JCId 2 is ParentID of JCId 6,7.
• Also JCId 3,4,5 are also ParentId's of 8,9,10,11,12 and so on.

Conditions:

• I want to get only those records from the JobCollection table whose JCId is not parent of any further records.

As highlighted in green border, JCId 8,9,10,11 and 12 are not parent of the any record

Also the green border highlights super child of JCId 1 and not JCId 2

Please note, this is an example and we can not use to stored procedure or cursor. And the hierarchy level is undefined. It can be anything.

Update:

One more Example

I want to get only those records highlighted in red. As you see the green border tells that those are the super child of every record but the Red highlights the Super child records of JCId 1

From bottom of heart I request to everyone please read the question carefully and understand the pain in it before down voting to the question. It is really hard for me to get the expected result

Answer 1

Perhaps a little more than you need, but you could slim it down if so desired.

The trick here is to use the Range Keys R1/R2.

Declare @YourTable table (JCId int,JCParentId  int,JCName varchar(50))
Insert into @YourTable values 
 ( 1, NULL,'A')
,( 2, NULL,'B')
,( 3, 1   ,'A1')
,( 4, 1   ,'A2')
,( 5, 1   ,'A3')
,( 6, 2   ,'B1')
,( 7, 2   ,'B2')
,( 8, 3   ,'A11')
,( 9, 3   ,'A12')
,(10, 4   ,'A21')
,(11, 5   ,'A31')
,(12, 5   ,'A32')
,(13, 6   ,'B11')
,(14, 6   ,'B12')
,(15, 7   ,'B21')
,(16, 7   ,'V22')

Declare @Top    int         = 1 --null      --<<  Sets top of Hier Try 3 
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by JCName) as varchar(500))
            ,JCId
            ,JCParentId 
            ,Lvl=1
            ,JCName 
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(JCParentId ,-1) else JCId end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.JCName)) as varchar(500))
            ,r.JCId
            ,r.JCParentId 
            ,p.Lvl+1
            ,r.JCName 
      From   @YourTable r
      Join   cteP p on r.JCParentId  = p.JCId)
     ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP)
     ,cteR2 as (Select A.JCId,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.JCId )
Select A.R1  
      ,B.R2
      ,A.JCId
      ,A.JCParentId 
      ,A.Lvl
      ,JCName = Replicate(@Nest,A.Lvl-1) + A.JCName
 From cteR1 A
 Join cteR2 B on A.JCId=B.JCId
 and R1=R2

Returns

---

Full Hierarchy when @Top = NULL and you remove the final and R1=R2