Excluding values using NOT IN not working

• shel 29143

  Mr or Mrs. 500

  Points: 502

  More actions

  March 25, 2019 at 6:50 am

  #2031630

  I posted last week or so about joining results to an existing table and I tried all the responses but nothing worked. I wanted to make a test mockup of data so it might explain it better

  CREATE TABLE test(

  TYPEvarchar (50),
[NAME]varchar(15),
PROVIDvarchar(15))

  INSERT INTO TEST (TYPE,NAME,PROVID) VALUES ('SECONDARY','SMITH','12345');
INSERT INTO TEST (TYPE,NAME,PROVID) VALUES ('PRIMARY','SMITH','12345');
INSERT INTO TEST (TYPE,NAME,PROVID) VALUES ('SECONDARY','JONES','67890');
INSERT INTO TEST (TYPE,NAME,PROVID) VALUES ('SECONDARY','HUNTER','19286');

  This should give:

  |TYPE|NAME|PROVID|

  |SECONDARY|SMITH|12345|
  |PRIMARY|SMITH|12345|
  |SECONDARY|JONES|67890|
  |SECONDARY|HUNTER|19286|

  I want to pull all records that have a Primary Status
  select * into #primary from test where type = 'primary'

  then I want to pull all records that have secondary type but not
  select * from test where type = 'secondary' and type not in (select type from #primary)

  When I run the last statement I get

  TYPE	NAME	PROVID
  SECONDARY	SMITH	12345
  SECONDARY	JONES	67890

  I should get Jones and Hunter not Smith and Jones. Smith has a primary type in his record and I don't know how to leave out people that have a secondary and primary type of status.

  Thanks for any help!

• @Taps

  SSC Eights!

  Points: 879

  More actions

  March 25, 2019 at 7:34 am

  #2031669

  I think this is what you meant and it does work
  
select * from #test where type = 'secondary'
AND [name] not in (select p.name from #primary p)


• drew.allen

  SSC Guru

  Points: 76985

  More actions

  March 25, 2019 at 8:02 am

  #2031674

  I posted the outline of a solution in your other thread, and it does work.  Here is the full solution given your test data.

  
WITH CTE AS
(
    SELECT [TYPE], [NAME], PROVID, ROW_NUMBER() OVER(PARTITION BY PROVID ORDER BY [TYPE]) AS rn
    FROM #Test
)
SELECT [TYPE], [NAME], PROVID
FROM CTE
WHERE rn = 1
    AND [TYPE] = 'Secondary'

  It only requires one scan of the table, whereas the other solution requires two.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA