Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

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
0
ethar1
Asked:
ethar1
  • 3
1 Solution
 
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
 
Scott PletcherSenior 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
 
ethar1Author Commented:
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now