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:
Get all encounters where at least one of the
DX_codein Table A is aDX_Codein 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 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.