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

score 1 · Answer 1 · answered Feb 03 '17 at 12:50

1

There are lot's of ways. Here is 1.

 select whatever
 from table t1 left join table t2 on jcid = jcparentid
 where t2.jcid is null

answered Feb 03 '17 at 12:50

Dan Bracuk

20,699
4
26
43

Using `t1` and `t2` really don't help here unless you explicitly state it's the same table with an alias. – DavidG Feb 03 '17 at 12:57
As I mentioned I want to get only those records from the JobCollection table whose JCId is not parent of any further records AND whose Super Parent JCId is 1. – Charan Ghate Feb 03 '17 at 12:57

score 1 · Accepted Answer · answered Feb 03 '17 at 13:15

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

This is just fabulous answer John. But I have concern about those people who have down voted my question as this was really hard for me to implement — Charan Ghate, Feb 03 '17 at 13:25
@CharanGhate Your question didn't ask for this in it's first version, it's now completely different. — DavidG, Feb 03 '17 at 13:29

SqlZim · Answer 3 · 2017-02-03T14:00:33.227

Using a recursive cte to get all the decedents of one JCId, then selecting those that have no children of their own using not exists():

declare @ParentId int;
set @ParentId = 1;

with cte as (
  select  JCId, JCName, JCParentId, JCParentName
    from  JobCollection
    where JCId = @ParentId
  union all
  select c.JCId, c.JCName, c.JCParentId, c.JCParentName
    from JobCollection c
      inner join cte p on p.JCId = c.JCParentId 
)
select JCId, JCName, JCParentId, JCParentName
from cte as o
where not exists (
  select 1 
    from cte as i
    where o.JCid = i.JCParentId
  );

test setup: http://rextester.com/LGEQD6195

create table JobCollection (
    JCId int
  , JCName varchar(50)
  , JCParentId  int
  , JCParentName varchar(50)
);

insert into JobCollection values 
 ( 1, 'A'  , null, null)
,( 2, 'B'  , null, null)
,( 3, 'A1' , 1, null)
,( 4, 'A2' , 1, null)
,( 5, 'A3' , 1, null)
,( 6, 'B1' , 2, null)
,( 7, 'B2' , 2, null)
,( 8, 'A11', 3, null)
,( 9, 'A12', 3, null)
,(10, 'A21', 4, null)
,(11, 'A31', 5, null)
,(12, 'A32', 5, null)
,(13, 'B11', 6, null)
,(14, 'B12', 6, null)
,(15, 'B21', 7, null)
,(16, 'B22', 7, null);

query:

declare @ParentId int;
set @ParentId = 1;

with cte as (
  select  JCId, JCName, JCParentId, JCParentName
    from  JobCollection
    where JCId = @ParentId
  union all
  select c.JCId, c.JCName, c.JCParentId, JCParentName = p.JCName
    from JobCollection c
      inner join cte p on p.JCId = c.JCParentId 
)
select JCId, JCName, JCParentId, JCParentName
from cte as o
where not exists (
  select 1 
    from cte as i
    where o.JCid = i.JCParentId
  );

results:

+------+--------+------------+--------------+
| JCId | JCName | JCParentId | JCParentName |
+------+--------+------------+--------------+
|   11 | A31    |          5 | A3           |
|   12 | A32    |          5 | A3           |
|   10 | A21    |          4 | A2           |
|    8 | A11    |          3 | A1           |
|    9 | A12    |          3 | A1           |
+------+--------+------------+--------------+

Svekke · Answer 4 · 2017-02-03T12:59:22.603

0

I'll give it a try:

SELECT JcId FROM JobCollection
WHERE JcId NOT IN (SELECT JcParentId FROM JobCollection)

UPDATE: Select only when JcParentId = 1

SELECT JcId FROM JobCollection
WHERE JcId NOT IN (SELECT JcParentId FROM JobCollection)
AND JcParentId = 1

edited Feb 03 '17 at 12:59

answered Feb 03 '17 at 12:50

Svekke

1,470
1
12
20

Logically sound, but `not in` tends to be slow. – Dan Bracuk Feb 03 '17 at 12:50
True, but it gives the desired result. As you said...there are lots of ways to do this :-) – Svekke Feb 03 '17 at 12:51
@Svekke Where do I add a JCId = 1 condition as I want only those records whose super parent JCId is 1 – Charan Ghate Feb 03 '17 at 12:54
@Svekke try to run the updated query yourself by preparing the data I given as it is giving null result to me on my data. the query is upto the level one only. the data level can be upto N number – Charan Ghate Feb 03 '17 at 13:17

score 0 · Answer 5 · edited Feb 03 '17 at 14:56

0

This should work!

SELECT  distinct J1.* 
from  JobCollection J1
LEFT JOIN  JobCollection J2 ON J1.JCId = J2.JcParentId
WHERE  J2.JcParentId IS NULL

edited Feb 03 '17 at 14:56

Thangadurai.B

561
1
3
18

answered Feb 03 '17 at 13:32

Ocean

1

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

5 Answers5