Better solution than left join subqueries?

Question

TablePatient.Patient_ID(PK)

TableProviders.Encounter (joins to PK)

TableProviders.Provider_Type

TableProviders.Provider_ID

TableNames.Full_Name

TableNames.Provider_ID (joins to Table Names)

I want a query that will give, for all the Patient_IDs, the Full_Name of the provider for every Provider ID.

There are about 30 provider_types.

I have made this already using a left join a ton of left joins. It takes a long time to run and I am thinking there is a trick I am missing.

Any help?

Answer 1

Ok, my previous answer didn't match at all what you meant. You want to pivot the table to have on each line one Patient_ID with every Full_name for every provider_type. I assume that each patient has only one provider for one type and not more ; if more, you will have more than one row for each patient, and anyway I don't think it's really possible.

Here is my solution with pivot. The first part is to make it more understandable, so I create a table named TABLE_PATIENT in a subquery.

WITH TABLE_PATIENT AS
(   
SELECT TablePatient.Patient_ID,
TableProviders.Provider_Type,
TableNames.Full_Name


FROM TablePatient LEFT JOIN
TableProviders on TablePatient.Patient_ID = TableProviders.Encounter 
LEFT JOIN
TableNames on TableNames.Provider_ID = TableProviders.Provider_ID

group by TablePatient.Patient_ID,
TableProviders.Provider_Type,
TableNames.Full_Name
)

SELECT *
FROM TABLE_PATIENT
PIVOT
(
    min(Full_name)
    for Provider_type in ([type1], [type2],[type3])
) AS PVT

So TABLE_PATIENT just has many rows for each patient, with one provider each row, and the pivot puts everything on a single row. Tell me if something doesn't work.

You need to write every type you want in the [type1],[type2] etc. Just put them inside [], no other character needed as ' or anything else.

If you put only some types, then the query will not show providers of other types.

Tell me if something doesn't work.