Not In statement not working

shelbsassy · March 25, 2019, 12:34pm

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(

TYPE varchar (50),
[NAME] varchar(15),
PROVID varchar(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|

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!

ScottPletcher · March 25, 2019, 1:45pm

select * 
from #test t 
where type = 'secondary' and
    not exists(select 1 from #primary p where p.provid = t.provid)

nigelrivett · March 25, 2019, 4:52pm

select t1.*
from test t1
left join test t2
on t2.type = 'primary
and t1.provid = t2.provid
where t1.type = 'secondary'
and t2.provid is null

with cte as
(
select provid
from test
group by provid
having sum(case when type = 'secondary' then 1) > 0
and sum(case when type = 'primary' then 1) = 0
)
select t1.*
from test t1
join cte t2
on t1.provid = t2.provid
where t1.type = 'secondary'

with cte as
(
select *, i = row_number() over (partition by provid order by type)
from test
where type in ('primary','secondary')
)
select * from cte where i = 1 and type = 'secondary'

Or - as you want all primary and secondary I would do them in a single table then split when you access them
with cte as
(
select *, i = row_number() over (partition by provid order by type)
from test
where type in ('primary','secondary')
)
select *
into #recs
from cte
where i=1

Then you have all the primary rows and all the secondary which don't have a primary and you can access the two datasets by using type.

shelbsassy · March 25, 2019, 6:19pm

Hi @nigelrivett
Thanks so much for your response. I got the first part to work and give me exactly what I needed.
select t1.*
from test t1
left join test t2
on t2.[type] = 'primary'
and t1.provid = t2.provid
where t1.[type] = 'secondary'
and t2.provid is null

However, when I apply this to my actual data I get zero rows. Which has been the case in every way I have tried this scenario.

Here is what I am trying to work with:

---NO SPECIALTY DECLARED--GET LIST OF PRIMARIES TO USE AS ELIMINATION--

select
ps.provid, fullname
into #primary
from qnxt.dbo.provspecialty ps
join qnxt.dbo.provider p on ps.provid = p.provid
where spectype = 'PRIMARY'

THEN BASE TABLE:

--BASE TABLE

select distinct
mp.Affiliationid,
a1.provid as "PCP Provid",
p1.Fullname,
p1.Provtype,
a1.Affiltype,
a1.affiliateid as "PCP Affiliate",
p2.fullname as Fullname2,
p2.provtype as Provtype2,
a1.payflag as Payflag1,
a1.PCP,
mp.Enrollid,
mp.Pcptype,
y.spectype,
mp.Specialtycode as MP_SpecialtyCode,
z.description as 'MP Specialty Description',
PS_SpecialtyCode = '',
mp.Paytoaffilid,
a2.provid as "Payto Provid",
p3.fullname as Fullname3,
p3.provtype as Provtype3,
a2.affiltype as Affiltype2,
a2.affiliateid as "Payto Affiliate",
a2.payflag,
p4.fullname as Fullname4,
p4.provtype as Provtype4,
a2.payflag as Payflag2,
a2.pcp as PCP2,
cast(mp.effdate as date) as PCPEffdate,
cast(mp.termdate as date) as PCPTermdate,
mp.Svczip,
mp.Createid,
mp.Createdate,
mp.Lastupdate,
mp.Updateid,
ci.ContractID,
cast(ek.effdate as date) as EnrollEff,
cast(ek.termdate as date) as EnrollTerm,
Flag = ''
into #BASE
from qnxt.dbo.memberpcp mp
join qnxt.dbo.affiliation a1 (nolock) on mp.affiliationid = a1.affiliationid
join qnxt.dbo.affiliation a2 (nolock) on mp.paytoaffilid = a2.affiliationid
join qnxt.dbo.provider p1 (nolock) on a1.provid = p1.provid
join qnxt.dbo.provider p2 (nolock) on a1.affiliateid = p2.provid
join qnxt.dbo.provider p3 (nolock) on a2.provid = p3.provid
join qnxt.dbo.provider p4 (nolock) on a2.affiliateid = p4.provid
join qnxt.dbo.provspecialty y (nolock) on p1.provid = y.provid
join qnxt.dbo.specialty z (nolock) on mp.specialtycode = z.specialtycode
left join qnxt.dbo.affiliation_audit aa (nolock) on a1.affiliationid = aa.affiliationid
left join qnxt.dbo.provider pr (nolock) on aa.provid = pr.provid
left join qnxt.dbo.entity e (nolock) on p1.entityid =e.entid
join QNXT.[dbo].[enrollkeys] ek (nolock) on mp.enrollid = ek.enrollid
left join qnxt.dbo.contractinfo ci (nolock) on mp.paytoaffilid = ci.affiliationid and ek.programid = ci.programid
join QNXT.dbo.memberpcp x (nolock) on x.enrollid = ek.enrollid
where mp.termdate > '2017-01-01'

THEN

SELECT DISTINCT
Affiliationid,
[PCP Provid],
Fullname,
Provtype,
Affiltype,
[PCP Affiliate],
Fullname2,
Provtype2,
Payflag1,
PCP,
Enrollid,
Pcptype,
b.Spectype,
MP_SpecialtyCode,
[MP Specialty Description],
PS.Specialtycode as PS_SpecialtyCode,
Paytoaffilid,
[Payto Provid],
Fullname3,
Provtype3,
Affiltype2,
[Payto Affiliate],
Payflag,
Fullname4,
Provtype4,
PCP2,
b.PCPEffdate,
b.PCPTermdate,
Svczip,
b.Createid,
b.Createdate,
b.Lastupdate,
b.Updateid,
ContractID,
EnrollEff,
EnrollTerm,
Flag = 'PCP with no primary specialty declared'
INTO #NOPRIMARY
FROM #BASE b
JOIN QNXT.DBO.PROVSPECIALTY PS (nolock) ON PS.PROVID = B.[PCP PROVID]

The final table is #NoPrimary. This is part of 16 queries that are union-ed together to flag different scenarios. This particular segment is trying to figure out which PCPs do not have a Primary Specialty declared, and only have a Secondary Specialty declared.

If anyone has any other ways to work through this I would welcome any suggestions. I just can't figure out why this will not work on the test data and not the real data.

Thanks so much!!!

nigelrivett · March 26, 2019, 2:02pm

I can't see anything that checks there is a secondary without a primary there.
I would change this to just get the IDs that you want then using them get the other data.
This will mean that the query to get the data is as simple as possible and so is easy to understand and as it does not access dependent data is more likely to use indexes and also produce a minimal set of data. It will be easier to understand and to debug.
Then you can get the other data (names, dates etc.) which aren't important to the selection.

To find out why you are not getting any rows ignore the select values - just put in a count(*). Then start commenting out the joins until you find which is causing a zero count.

harishgg1 · March 26, 2019, 3:20pm

hi

i tried to do this

hope it helps

drop create data ..

drop table test 
go

CREATE TABLE test(
TYPE	varchar (50),
[NAME]	varchar(15),
PROVID	varchar(15))
go 

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');
go 

select * from test 
go

SQL

select name from test a where type = 'SECONDARY'
except
select name from test a where type = 'PRIMARY'

Lewie · March 26, 2019, 10:23pm

select *
from #test t
where provid NOT IN (SELECT DISTINCT A.provid FROM #test A JOIN #test B ON A.provid = B.provid AND A.Type = 'secondary' AND B.Type = 'primary')