Issue

This Content is from Stack Overflow. Question asked by Josh

I have two tables with the following format below:

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.

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

I think I have to do this in two steps:

Get all encounters where at least one of the DX_code in Table A is a DX_Code in Table B.
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 should have the following format (or whatever that works):

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

I am not sure where to start. So far, I think the solution is to 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
)

The code runs but I am getting encounters where NONE of the linked diagnoses are from the heart disease table.
Any help is appreciated! Thank you!

Solution

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

This Question was asked in StackOverflow by Josh and Answered by Wolf D. It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.