select * rows has no Childe

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
ethar1Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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 )
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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?
0
 
ethar1Author 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
0
 
ethar1Author Commented:
I just need delete all records like DicWordID= 4082
it has no Childe
Untitled-1.png
0
 
ethar1Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.