Hi Rnisha - This query depends on what provider you are looking for. If you want the PCP of the Patient, you would use the query below.
The PCP is housed in the Patient_Member table as ProviderID. In the PCPTYPE field, if the value is R then this is a outside PCP, and if it is a P then it is an internal provider. You can link this back to find the provider's name by joining on the Person table again, as all internal providers and patients names are stored in the Person table. The Referring_Provider_DE table houses the names of the external/referring providers.
pat.LastName+', '+pat.FirstName 'PatientName',
CASE WHEN PCPTYPE = 'R' THEN ISNULL(rpd.EntryName,'')
ELSE Per.LastName+', '+per.FirstName
END AS 'PCPName'
INNER JOIN Person pat ON pat.ID = pm.ID
LEFT OUTER JOIN Person per ON pm.ProviderID = per.ID
LEFT OUTER JOIN dbo.Referring_Provider_DE rpd ON rpd.ID = pm.ProviderID