Subquery Needed?

Using the sql script below in SQL Query Analyzer to determine the member’s primary care physician, the physician who provided the service and the medical group (IPA) of the primary care physician.

Select
t.MEMBER_NBR
, pr.PROV_ID as SvcProviderID
, pr.PROV_RELATION_PROV_ID as PCPProvID
,pti.PROVIDER_TAX_ID -- Is this the tax id of the PCP?
,case when PTI.PROVIDER_TAX_ID = '3006562' THEN 'CV_PHYS'
ELSE 'NOT_CV_PHYS' END AS PhysCVPHYS
, g.PROV_FULL_NM – Should be name of the medical group

From CLAIM a (NOLOCK)
inner join MEMBER_SOURCE t (NOLOCK) on a.MEMBER_ID = t.MEMBER_ID
and t.LOGICAL_DELETE_IND <> 'Y'

inner join PROVIDER g (NOLOCK) on a.CLM_LINE_SVC_PROV_ID = g.PROVIDER_ID
and g.LOGICAL_DELETE_IND <> 'Y'

inner join PROVIDER_RESET pr on g.PROVIDER_ID = pr.PROVIDER_ID
and pr.LOGICAL_DELETE_IND <> 'Y'

inner join PROVIDER_TAX_ID pti on g.PROVIDER_ID = PTI.PROVIDER_ID
AND pti.LOGICAL_DELETE_IND <> 'Y'

where

t.MEMBER_NBR in ('2563896','3628974')
AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)

group by

t.MEMBER_NBR
, pr.PROVIDER_ID
, pr.PROV_RELATION_PROV_ID
, pti.PROVIDER_TAX_ID
, pr.PROV_RELATION_PROV_ID
, g.PROV_FULL_NM

Upon reviewing the query result set, as displayed below, it appears that I am extracting the name of the physician that provided the service not the medical group (IPA) of the primary care physician in the field titled "ProvFullNm." The contents of this field should be the name of the medical group (IPA)- something like "CV_Physician."

SrcSysMemNbr--SvcProvID--PCPProvID---PhysCVPhys--ProvFullNm
2563896-------5015-------48956-------NOT_CV_PHYS-John Doe
2563896-------5015-------49055-------NOT_CV_PHYS-John Doe
2563896-------5283-------46523-------NOT_CV_PHYS-Sally Fa
2563896-------5246-------46526-------NOT_CV_PHYS-Bill Sne



Additional Comments
"pr.PROV_RELATION_PROV_ID" is the provider_id for the primary care physician

Note, "pr.PROV_ID" is the provider_id for the physician who provided the service. Further, the provider_id for the physician who provided the service and the provider_id for the primary care physician is stored in the same table, "PROVIDER_RESET."

Of course, sometimes the physician who provide service is also the primary care physician.
If the PCP has a tax id of ‘3006562’, then the PCP has a CV_Physician medical group.

Questions
How do I definitely “pull” the pti.PROVIDER_TAX_ID for the primary care physician not the physician who provided the service?

Then, I can determine if the primary care physician’s medical group is “CV_Physician using the CASE statement.

My initial try at this was to include a subquery in the WHERE clause such as

“AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)”


Thanks in advance.