Trying to include the result set in the base table

Transact-SQL
1

I have a weird one that is hard to explain.
S‌o I am looking for any Providers that are not listed as a Primary. So I did a select PROVID from PROVSPECIALTY (#primary) table which gives me about 73000 rows of PROVIDs. Then I did a select PROVID from PROVSPECIALTY where SPECTYPE = 'SECONDARY' (#sec) which gives 3600 rows. Then I ran select distinct * from #sec where provid not in (select distinct * from #primary) which gives me what I want which is 192 rows.
W‌hat my question is is how can I join the 192 records to my base table to get all the fields needed for those provids?
I have tried every kind of join, where exists, etc.
‌
T‌his is the ‌base table that gets all the fields:

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, Spectype, mp.Specialtycode, z.description as 'Specialty Description', 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 left join qnxt.dbo.contractinfo ci (nolock) on mp.paytoaffilid = ci.affiliationid join QNXT.[dbo].[enrollkeys] ek (nolock) on mp.enrollid = ek.enrollid join QNXT.dbo.memberpcp x (nolock) on x.enrollid = ek.enrollid where mp.termdate > '2017-01-01'‌‌

I‌ wasn't sure what information to include in terms of data but if someone could give me a lead on how to solve this I will be happy to include test data. Thank you for any help!‌

2

are the 73000 PROVID distinct or do you actually, really have that many providers?
please list out the distinct list of SPECTYPE s

3

Yes distinct there are 72903.

4

You said "S‌o I am looking for any Providers that are not listed as a Primary."
how many types of SPECTYPE do you have

5

Maybe something like this...

   With primaryProviders
     As (
 Select ...
   From PROVSPECIALTY ps
  Where {identify 'primary' specialties
        )
      , specialtyProviders
     As (
 Select ...
   From PROVSPECIALTY ps
  Where {specialty identifiers}
    And provid Not In (Select ... From primaryProviders pp)
        )
 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
      , Spectype
      , mp.Specialtycode
      , z.description              As 'Specialty Description'
      , 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 specialtyProviders sp On sp.provid = mp.provid

   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
   Left Join qnxt.dbo.contractinfo ci(nolock) On mp.paytoaffilid = ci.affiliationid
   Join QNXT.[dbo].[enrollkeys] ek(nolock) On mp.enrollid = ek.enrollid
   Join QNXT.dbo.memberpcp x(nolock) On x.enrollid = ek.enrollid
  Where mp.termdate > '2017-01-01'‌‌

Or - if you can reduce your multiple temp tables into a single query...

  Where mp.termdate > '2017-01-01'‌‌
    And mp.provid In ({your query to identify just those providers to be included})

If you must use these temp tables:

  Where mp.termdate > '2017-01-01'‌‌
    And mp.provid In (Select sp.provid From #sec sp)

Or you can join to that temp table on provid...

6

I am trying to use the CTE but I am getting an error and I am not that familiar with CTEs.
I added in my fields but I am getting an error on the comma before specialtyproviders.

With primaryProviders
As (
Select PROVID
From qnxt.dbo.provspecialty ps
Where (SPECTYPE = 'PRIMARY'
)
, specialtyProviders
As (
Select PROVID
From qnxt.dbo.provspecialty ps
Where (SPECTYPE = 'SECONDARY')
And provid Not In (Select PROVID From primaryProviders pp)
)

7

Hi

I have formatted the SQL

Makes it easier to read and follow

hope it helps

:slight_smile:
:slight_smile:

SQL Formatted 
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, 
                spectype, 
                mp.specialtycode, 
                z.description AS 'Specialty Description', 
                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 
LEFT JOIN       qnxt.dbo.contractinfo ci (nolock) 
ON              mp.paytoaffilid = ci.affiliationid 
JOIN            qnxt.[dbo].[enrollkeys] ek (nolock) 
ON              mp.enrollid = ek.enrollid 
JOIN            qnxt.dbo.memberpcp x (nolock) 
ON              x.enrollid = ek.enrollid 
WHERE           mp.termdate >'2017-01-01'‌‌
8

Check your parentheses - you are missing at least one...and also make sure you use the alias with the column.

Select ps.PROVID
...
And ps.provid Not In (Select pp.PROVID From primaryProviders pp)

9

Ok I fixed the alias and parentheses issues and got it to run, but it is returning the same results and when I would write the select provid from provspecialty where provid not in select provid from secondary). It is just returning column headers. I get that I must be doing something wrong but what bothers me is I don't understand why it is returning zero rows. It seems like a pretty straightforward ask from SQL I mean :slight_smile:

10

When you say it is returning the same results - the same results as what? If you run just your base query - do you get results?

Try changing the join to the specialty Providers to an outer join and verify that you are seeing values from that table.

You can also modify the base query using an EXISTS:

AND EXISTS (SELECT *
              FROM qnxt.dbo.provspecialty ss
             WHERE ss.provid = mp.provid
               AND ss.SPECTYPE = 'SECONDARY'
               AND NOT EXISTS (SELECT *
                                 FROM qnxt.dbo.provspecialty ps
                                WHERE ps.provid = ss.provid
                                  AND ps.SPECTYPE = 'PRIMARY'))

If you are still not getting any results - then you need to start looking at the joins and other criteria being used.