Find all non-parent children

Question

I try to find all children who are not parents.

The table looks like this:

ID | ParentID --------------- 1 NULL 2 1 3 NULL 4 2 

I tried first

 SELECT * FROM [SMD].[dbo].[ProposalFollowUp] WHERE ID NOT IN (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp]) 

but it does not return a string. I wanted to select all rows that are not in parentID. I do not understand why it does not work.

Then i tried this

 SELECT * FROM [SMD].[dbo].[ProposalFollowUp] AS a WHERE a.ID NOT IN (SELECT b.ID FROM [SMD].[dbo].[ProposalFollowUp] as b WHERE b.ParentID = a.ID) 

but it returns all rows

Anyone can tell me what I am missing

Thanks!

Answer 1

Using not in provides a well-known SQL query:

 WHERE ID NOT IN (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp]) 

To understand why, run the query:

 WHERE ID NOT IN (null, 1, null, 2) 

And it translates as:

 where id <> null and id <> 1 and id <> null and id <> 2 

The trick is that id <> null never true. In SQL, three-valued logic evaluates to unknown . And that means your where clause never approves a single line.

To solve this problem, use exists (e.g. Tim Schmelter's answer) or exclude null from the subquery:

 WHERE ID NOT IN ( SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp] WHERE ParentID IS NOT NULL) 

Answer 2

You can use NOT EXISTS :

 SELECT ID, ParentID FROM [SMD].[dbo].[ProposalFollowUp] t1 WHERE NOT EXISTS ( SELECT 1 FROM [SMD].[dbo].[ProposalFollowUp] t2 WHERE t2.ParentID = t1.ID ) 

This returns only rows where the ID not a ParentID in another row. Therefore, it is not a parent.

Answer 3

 SELECT * FROM [SMD].[dbo].[ProposalFollowUp] WHERE ID NOT IN (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp] WHERE ParentID IS NOT NULL) 

Answer 4

why it doesn't work.

You do not extract rows from the first query:

 SELECT * FROM [SMD].[dbo].[ProposalFollowUp] WHERE ID NOT IN (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp]) 

Due to the NULL values ​​in the ParentID there becomes UNKNOWN for the predicate, so it returns nothing, you can avoid this using NOT EXISTS :

 SELECT * FROM [SMD].[dbo].[ProposalFollowUp] WHERE NOT EXISTS (SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp]) 

Unlike all other predicates in sQL, NOT EXISTS works on two logic values TRUE and FALSE , because there are only two possibilities for the value: exists (ture) or false, there is no way to return UNKNOWN .

There is another workaround that will not get what you are looking for in your case, that is, excluding these NULL values ​​using AND ParentID IS NOT NULL , but in your case you will not get the results you are looking for

Answer 5

If you only need the identifiers of the children (who are not parents), you can also use EXCEPT :

 SELECT ID FROM [SMD].[dbo].[ProposalFollowUp] EXCEPT SELECT ParentID FROM [SMD].[dbo].[ProposalFollowUp] ; 

Answer 6

Relace = on <>

 SELECT * FROM [SMD].[dbo].[ProposalFollowUp] AS a WHERE a.ID NOT IN (SELECT b.ID FROM [SMD].[dbo].[ProposalFollowUp] as b WHERE b.ParentID <> a.ID)