Lookup and conditional JOIN in SELECT Query

I have a table th开发者_开发百科at contains three pieces of patient information.

Diagnosis_ID      Patient_ID        Diagnosis_Code
====================================================
1                 Jenkins, Sam          A743
2                 Smith, Fred           D638
3                 Doe, John             G732

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.

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

Normally I could just JOIN to the table that relates Diagnosis_Code to Diagnosis_Description and I would be good to go. However, it is not that easy. The value for Diagnosis_Description is located in one of three possible tables. Determining which table I need to JOIN on depends on what the Diagnosis_ID is.

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.

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

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#)?

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.

Multiple LEFT JOINs? For each row in the main table, this will choose the appropiate LEFT JOIN onto the correct DiagnosisTable

SELECT
    T.*, 
    COALESCE(D1.Diagnosis_Description, D2.Diagnosis_Description, D3.Diagnosis_Description)
FROM 
    MyTable T
    LEFT JOIN
    DiagnosisTable1 D1 ON T.Diagnosis_Code = D1.Diagnosis_Code
                   AND T.DiagnosisLevel = 'DiagnosisTable1'
    LEFT JOIN
    DiagnosisTable2 D2 ON T.Diagnosis_Code = D2.Diagnosis_Code
                   AND T.DiagnosisLevel = 'DiagnosisTable2'
    LEFT JOIN
    DiagnosisTable3 D3 ON T.Diagnosis_Code = D3.Diagnosis_Code
                   AND T.DiagnosisLevel = 'DiagnosisTable3'

Let's call your first table patient_diagnosis. There are two approaches I would take the first if the Diagnosis_code will only appear in one of the three Diagnosis tables

select
  aa.Diagnosis_ID,
  aa.Patient_ID,
  aa.Diagnosis_Code,
  coalesce(bb.Diagnosis_Description,    cc.Diagnosis_Description,  
    dd.Diagnosis_Description)     as    Diagnosis_Description
from
  patient_diagnosis as aa
left join
  DiagnosisTable1 as bb on aa.Diagnosis_Code = bb.Diagnosis_Code
left join
  DiagnosisTable2 as cc on aa.Diagnosis_Code = cc.Diagnosis_Code
left join
  DiagnosisTable3 as dd on aa.Diagnosis_Code = dd.Diagnosis_Code

The second is similar but actually uses the look-up table

select
  aa.Diagnosis_ID,
  aa.Patient_ID,
  aa.Diagnosis_Code,
  case when ee.Diagnosis_Level = 1 then bb.Diagnosis_Description
  when ee.Diagnosis_Level = 2 then cc.Diagnosis_Description
  when ee.Diagnosis_Level = 3 then dd.Diagnosis_Description
  else 'Error' end as    Diagnosis_Description
from
  patient_diagnosis as aa
left join
  DiagnosisTable1 as bb on aa.Diagnosis_Code = bb.Diagnosis_Code
left join
  DiagnosisTable2 as cc on aa.Diagnosis_Code = cc.Diagnosis_Code
left join
  DiagnosisTable3 as dd on aa.Diagnosis_Code = dd.Diagnosis_Code
join
  DiagnosisDetail as ee on aa.Diagnosis_ID = ee.Diagnosis_ID

You can virtually merge all the 3 diagnosis level tables, then join to it. Change LEFT to INNER JOIN if you must have matches, otherwise LEFT JOIN doesn't hurt.

SELECT A.Diagnosis_ID, A.Patient_ID, A.Diagnosis_Code, B.Diagnosis_Description
FROM Diagnosis A
left join
(
    SELECT DiagnosisLevel = 'DiagnosisTable1', Diagnosis_Code, Diagnosis_Description
    FROM DiagnosisTable1
    UNION ALL
    SELECT DiagnosisLevel = 'DiagnosisTable2', Diagnosis_Code, Diagnosis_Description
    FROM DiagnosisTable2
    UNION ALL
    SELECT DiagnosisLevel = 'DiagnosisTable3', Diagnosis_Code, Diagnosis_Description
    FROM DiagnosisTable3
) B on A.Diagnosis_Level = B.DiagnosisLevel and A.Diagnosis_Code = B.Diagnosis_Code

While this looks deceptively like it will be an expensive operation, it is actually very very efficient. The UNION all form with a splitter column (DiagnosisLevel) is a partitioning strategy, which SQL Server uses to choose only from the table it needs to look into. For example, here is a test.

create table Diagnosis(
    Diagnosis_ID int primary key, 
    Patient_ID varchar(100), 
    Diagnosis_Code varchar(10), 
    Diagnosis_Level varchar(100))
insert Diagnosis select
    '1' ,'Jenkins, Sam' ,'A743','DiagnosisTable1' union all select
    '2' ,'Smith, Fred' ,'D638','DiagnosisTable2' union all select
    '3' ,'Doe, John' ,'G732','DiagnosisTable3'
create table DiagnosisTable1(id int identity primary key,Diagnosis_Code varchar(10),Diagnosis_Description varchar(100))
create index ix_DiagnosisTable1 on DiagnosisTable1(Diagnosis_Code) include (Diagnosis_Description)
insert DiagnosisTable1 select
    'A748', 'Sleep Apnea 1' union all select
    'A745', 'Sleep Apnea 2' union all select
    'A746', 'Sleep Apnea 3' union all select
    'A743', 'Sleep Apnea'
insert DiagnosisTable1 select top 1000
    Diagnosis_Code + left(CONVERT(varchar(max), newid()),3),
    Diagnosis_Description + CONVERT(varchar(max), newid())
from DiagnosisTable1 cross join master..spt_values
create table DiagnosisTable2(id int identity primary key,Diagnosis_Code varchar(10),Diagnosis_Description varchar(100))
create index ix_DiagnosisTable2 on DiagnosisTable2(Diagnosis_Code) include (Diagnosis_Description)
insert DiagnosisTable2 select
    'D6381', 'High Blood Pressure 1' union all select
    'D638', 'High Blood Pressure 3' union all select
    'D6384', 'High Blood Pressure'
insert DiagnosisTable2 select top 1000
    Diagnosis_Code + left(CONVERT(varchar(max), newid()),3),
    Diagnosis_Description + CONVERT(varchar(max), newid())
from DiagnosisTable2 cross join master..spt_values
create table DiagnosisTable3(id int identity primary key,Diagnosis_Code varchar(10),Diagnosis_Description varchar(100))
create index ix_DiagnosisTable3 on DiagnosisTable3(Diagnosis_Code) include (Diagnosis_Description)
insert DiagnosisTable3 select
    'G732', 'HDislocated Jointe 1' union all select
    'D6X8', 'HDislocated Joint 3' union all select
    'GGG84', 'Dislocated Joint'
insert DiagnosisTable3 select top 1000
    Diagnosis_Code + left(CONVERT(varchar(max), newid()),3),
    Diagnosis_Description + CONVERT(varchar(max), newid())
from DiagnosisTable3 cross join master..spt_values

==========
Summary
==========
3x diagnosis records
4000, 3000 and 3000 records in each of the level 1,2,3 tables

The query (repeated from above)

SELECT A.Diagnosis_ID, A.Patient_ID, A.Diagnosis_Code, B.Diagnosis_Description
FROM Diagnosis A
left join
(
    SELECT DiagnosisLevel = 'DiagnosisTable1', Diagnosis_Code, Diagnosis_Description
    FROM DiagnosisTable1
    UNION ALL
    SELECT DiagnosisLevel = 'DiagnosisTable2', Diagnosis_Code, Diagnosis_Description
    FROM DiagnosisTable2
    UNION ALL
    SELECT DiagnosisLevel = 'DiagnosisTable3', Diagnosis_Code, Diagnosis_Description
    FROM DiagnosisTable3
) B on A.Diagnosis_Level = B.DiagnosisLevel and A.Diagnosis_Code = B.Diagnosis_Code

The execution plan

You are really close already actually

In your select just do this

Select 
    Diagnosis_ID,
    Patient_ID,
    Diagnosis_Code,
    (Case
     When Level.DiagnosisLevel = "DiagnosisTable1" Then
        SELECT Diagnosis_Description FROM DiagnosisTable1 
     When Level.DiagnosisLevel = "DiagnosisTable2" Then
        SELECT Diagnosis_Description FROM DiagnosisTable2 
     When Level.DiagnosisLebel = "DiagnosisTable3" Then
     --Or you could just do an Else
        SELECT Diagnosis_Description FROM DiagnosisTable3 
     End) As Diagnosis_Description
    From MainTable
    Join TableThatHasLevels As Level ON Level.Diagnosis_Code = Diagnosis_Code

You should just be able to join the query that gets the diagnosis table info and do that