Better solution than left join subqueries?
<pre><code>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)
</code></pre>

<p>I want a query that will give, for all the <code>Patient_IDs</code>, the <code>Full_Name</code> of the provider for every <code>Provider ID</code>.</p>

<p>There are about 30 <code>provider_types</code>.</p>

<p>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.  </p>

<p>Any help?</p>