In SQL, how to join multiple foreign keys IDs in multiple columns of a table to a single primary key in another table and uniquely select one?

Viewed 36

Table A: Patient Encounters With Linked Diagnoses(DX)

Encounter_ID Date Primary_DX DX_2 DX_3 DX_4
11111 01/01/2020 234234 256756 254537 678688
11112 05/01/2020 344564 234553 6786667 234234
11113 01/01/2022 123233 656444 678688 535465
11114 01/01/2021 435345 666654 3453453 456448

Table B: Diagnoses(DX) Code Linked with Their respective ICD Code

NOTE: The codes for this table is filtered for DX_ID/ICD_CODE's specifically for heart disease.

DX_ID ICD_CODE
234234 N123.42
344564 N45.32
234553 N153.24
678688 N365.34

I seek to get only the encounters with the following condition:

At least one of the Primary_DX, DX_2,DX_3,DX_4 codes in Table A is a heart disease, that is, their respective diagnosis code can be linked to table B.

From this list, I seek to only get the ICD_Code for only that heart disease diagnosis code.

I have to do this in two steps:

  1. Get all encounters where at least one of the DX_code in Table A is a DX_Code in Table B.

  2. From this temporary table, select only the heart disease code and retrieve the ICD_code. If there are multiple heart disease for a single encounter, then they will show up as two separate rows.

So final output could have the following format:

Encounter_ID ICD_CODE
11111 N123.42
11111 N45.32
11112 N123.42
11115 N15.42
11114 N123.42

Now filter for heart disease dx_codes with the EXISTS cause as below:

SELECT
    Enounter_ID,
    Primary_DX,
    DX_2,
    DX_3,
    DX_4,
FROM 
    TABLE_A
WHERE   
    EXISTS (SELECT 1 FROM TABLE_B)

But I am getting encounters where NONE of the linked diagnoses are from the heart disease table.

1 Answers

Wouldn't this be enough ?

select Encounter_ID, ICD_CODE 
  from Table_A, Table_B
 where Primary_DX = DX_ID
    or DX_2 = DX_ID
    or DX_3 = DX_ID
    or DX_4 = DX_ID
order by Encounter_ID
Related