I have below table:
DECLARE @P AS TABLE
(
ID int,
PID int,
PNAME NVARCHAR(30),
PARENT_PNAME NVARCHAR(30),
Error int
)
INSERT INTO @P VALUES
(554,1,'AAAA',NULL,0),
(554,2,'BBBB',NULL,0),
(554,3,'CCCC',NULL,0),
(554,4,'DDDD','AAAA',0),
(554,5,'EEEE','AAAA',0),
(554,6,'FFFF',NULL,0),
(554,7,'GGGG',NULL,0),
(554,8,'HHHH',NULL,0),
(554,9,'IIII',NULL,0),
(554,10,'JJJJ',NULL,0),
(554,11,'KKKK',NULL,0);
If I run below query:
select ID, PID, PNAME, PARENT_PNAME, Error
from @P
where PNAME not in (select PARENT_PNAME
from @P
where PNAME is not null)
AND PARENT_PNAME IS NOT NULL
I get nothing, no result. Why "NOT IN" is not working?
I need to obtain the two below rows:
(554,4,'DDDD','AAAA',0)
(554,5,'EEEE','AAAA',0)