title: Lookup and conditional JOIN in SELECT Query
body: <p>I have a table that contains three pieces of patient information. </p>
  
  <pre><code>Diagnosis_ID      Patient_ID        Diagnosis_Code
  ====================================================
  1                 Jenkins, Sam          A743
  2                 Smith, Fred           D638
  3                 Doe, John             G732
  </code></pre>
  
  <p>I would like to query this table but also add a third column, "Diagnosis Description" that is linked to the diagnosis code in another table. </p>
  
  <pre><code>Diagnosis_ID      Patient_ID        Diagnosis_Code     Diagnosis_Description
  =============================================================================
  1                 Jenkins, Sam          A743             Sleep Apnea
  2                 Smith, Fred           D638             High Blood Pressure
  3                 Doe, John             G732             Dislocated Joint
  </code></pre>
  
  <p>Normally I could just <code>JOIN</code> to the table that relates <code>Diagnosis_Code</code> to <code>Diagnosis_Description</code> and I would be good to go. However, it is not that easy. The value for <code>Diagnosis_Description</code> is located in one of three possible tables. Determining which table I need to JOIN on depends on what the Diagnosis_ID is.</p>
  
  <p>So what I have to do currently is for each Diagnosis_ID I have to query another table that tells me which table I need to join Diagnosis_Code on based on Diagnosis_Level and then once I have that I can get Diagnosis_Description. Here is some "pseudo-code" that I hope describes the process.</p>
  
  <pre><code>SELECT DiagnosisLevel FROM DiagnosisDetail WHERE Diagnosis_ID = 1
  
  If DiagnosisLevel = "DiagnosisTable1" Then
    SELECT Diagnosis_Description FROM DiagnosisTable1 WHERE Diagnosis_Code = 'A743'
  ElseIf DiagnosisLevel = "DiagnosisTable2" Then
    SELECT Diagnosis_Description FROM DiagnosisTable2 WHERE Diagnosis_Code = 'A743'
  ElseIf
    SELECT Diagnosis_Description FROM DiagnosisTable3 WHERE Diagnosis_Code = 'A743'
  End If
  </code></pre>
  
  <p>I have no idea of how to do all this in SQL. Is it even possible or am I going to have to do it all in code (i.e. C#)?</p>
  
  <p>I apologize if this seems vague. I tried my best to create a simple example to demonstrate the problem. I didn't design this database, but have to work within its structure.</p>