select * rows has no Childe

ethar1
ethar1 used Ask the Experts™
on
Dear all,
I need to get all records that has no reference in same table?

DicWords: DicWordID - ParentDicWordID

I need to list all records in Words where DicWordID not in ParentDicWordID
I use this:
select * from DicWords
where DicWordID not  in (select ParentDicWordID from DicWords  )
 
another saying : select all records has no Childe
thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
The select statement you posted meets the requirements as you state them, so please clarify what you're trying to pull off here.  Some mockup data would be helpful.

>I need to list all records in Words
Is Words a separate table?

Author

Commented:
Words is same DicWords
when I use :
select * from DicWords
where DicWordID not  in (select ParentDicWordID from DicWords  )
it return nothing
where :
 select * from DicWords where DicWordID=878251 return a record
and
 select * from DicWords where ParentDicWordID=878251 return nothing

Author

Commented:
I just need delete all records like DicWordID= 4082
it has no Childe
Untitled-1.png
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Be sure to exclude any NULL value from NOT IN, as a NULL value can cause a NULL result:

SELECT *
--DELETE FROM DicWords
FROM DicWords
WHERE
    DicWordID NOT IN ( SELECT ParentDicWordID FROM DicWords  WHERE ParentDicWordID IS NOT NULL )

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial