Select all rows where columns have all values in a list

Question

Sorry for the lack of a better question title, I'll change it if someone knows the SQL jargon of what I'm trying to do to make it Google-able.

I have the following table from a hospital.

 subject_id | hadm_id | icd9_code ------------+---------+----------- 3 | 145834 | 5849 3 | 145834 | 4280 9 | 150750 | 5849 9 | 150750 | 4019 9 | 150750 | 4280 12 | 112213 | 4019 13 | 143045 | 4019 13 | 143045 | 25000 13 | 143045 | 41401 17 | 161087 | 2724 17 | 194023 | 2724

where subject_id is unique to a patient and hadm_id is unique to a patient admission to the hospital (a hospital stay). Each icd9_code represents a disease. So, for example, patient number 3 has been diagnosed with two diseases (5849 and 4280) in their only hospital stay.

I'm given a list of disease codes and I need to return the list of patients that have been diagnosed with at least all of those diseases.

Note that diagnoses may be repeated for the same patient (in the example, patient 17 has been diagnosed with disease 2724 twice on two different hospital stays).

dazedviper · Accepted Answer · 2018-04-18 14:57:07Z

You can use Postgres' array handling for this:

select subject_id from the_table group by subject_id having array_agg(distinct icd9_code) @> array[4280, 5849];

array_agg() collects all codes for each subject_id and the operator @> checks if that array contains all elements from the other expression.

I don't use much Postgres. I didn't realize it had real array handling. That is pretty cool.

Gurwinder Singh · Accepted Answer · 2018-04-18 13:49:18Z

You can filter the patients with the given disease list and check if the distinct count of disease match with the provided list count per stay:

select subject_id, hadm_id from your_table where icd9_code in (5849, 5850, 5851) group by subject_id, hadm_id having count(distinct icd9_code) = 3;

Collectives™ on Stack Overflow

Select all rows where columns have all values in a list

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related