I want select records that have GUID and not exists into parentGUID

enter image description here

i tried this code

select code, GUID, ParentGUID
from ac00
where NOT EXISTS (select ParentGUID from ac00 where GUID <> ParentGUID)
order by Code asc

CodePudding user response：

You were close but missing aliases.

select code,GUID,ParentGUID 
from ac00 t1 
where NOT EXISTS (select * from ac00 t2 
   where t2.GUID = t1.ParentGUID) 
order by Code asc;

CodePudding user response：

You can do it in a faster way. Joins are usually done much faster than inner queries

select ac1.Code,ac1.GUID,ac1.ParentGUID 
from ac00 ac1 LEFT OUTER JOIN dbo.ac00 ac2
ON ac2.Guid = ac1.ParentGuid
WHERE ac2.Code IS NULL