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.

[![enter image description here](https://i.stack.imgur.com/224wZ.png)](https://i.stack.imgur.com/224wZ.png)

**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

[![enter image description here](https://i.stack.imgur.com/79ktn.png)](https://i.stack.imgur.com/79ktn.png)

> 
> 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: 
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

```
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

```