CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Marc
Marc

Reputation: 16512

Find all children who are not parent

I'm trying to retrieve all children who are not parent.

The table looks like this

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

At first I tried

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

but it returns no row. I wanted to select all rows that are not in parentID. I don't understand why it's not working.

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 this returns all rows

Anyone can tell me what I'm missing

Thank you!

Upvotes: 4

Views: 1688

Answers (6)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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)

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

If you want only the IDs 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] ;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

why it's not working.

You are retrieving no rows from the first query:

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

Because of the NULL values in the ParentID there for the predicate becomes UNKNOWN therefore returns nothing, you can avoid this by using NOT EXISTS:

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

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

There is also another workaround, that won't get what you are looking for in your case, which is, by eliminating these NULL values using AND ParentID IS NOT NULL but in your case won't get you the results you are looking for

Upvotes: 1

Andomar
Andomar

Reputation: 238076

Using not in exposes a well-known SQL quirk:

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

To understand why, exand the query:

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

And that translates to:

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

The trick is that id <> null is never true. In SQL's three-valued logic, it evaluates to unknown. And that means your where clause never approves any row.

To solve this, use exists (like 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)

Upvotes: 8

Tim Schmelter
Tim Schmelter

Reputation: 460108

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 is not the ParentID in another row. Hence this is not a parent.

Upvotes: 3

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

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

Upvotes: 1

Related Questions