Q:

How to get the list of ICUSTAY_IDs that happened in a given HADM_ID?

In the MIMIC-III database, I wonder whether there is an easy way to obtain the list of ICUSTAY_IDs that happened in a given HADM_ID.

A:

In MIMIC 2V30, to get to the list of ICUSTAY_IDs that happened in a given HADM_ID, you can use:
SELECT t1.HADM_ID, t2.ICUSTAY_ID
FROM MIMIC2V30.ADMISSIONS t1
INNER JOIN MIMIC2V30.ICUSTAYEVENTS t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.ADMIT_DT <= t2.INTIME
AND t1.DISCH_DT >= t2.OUTTIME
ORDER BY t1.HADM_ID, t2.ICUSTAY_ID ASC;

or maybe to account for the fact that ICUSTAYEVENTS.OUTTIME is often after ADMISSIONS.DISCH_DT:
SELECT t1.HADM_ID, COUNT(DISTINCT(t2.ICUSTAY_ID)) cnt
FROM MIMIC2V30.ADMISSIONS t1
INNER JOIN MIMIC2V30.ICUSTAYEVENTS t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.ADMIT_DT <= t2.INTIME
AND CAST(t2.OUTTIME AS DATE)- CAST(t1.DISCH_DT AS DATE) < 1
GROUP BY t1.HADM_ID
ORDER BY cnt DESC;

In MIMIC-III, it's much easier:
-- returns 61532 rows
SELECT HADM_ID, ICUSTAY_ID FROM  mimiciii.icustays 
ORDER BY HADM_ID, ICUSTAY_ID ASC;

It can still use the same strategy as the one used a for MIMIC2V30, but it will yield a less complete list:
-- returns 49185 rows
SELECT t1.HADM_ID, t2.ICUSTAY_ID
FROM mimiciii.admissions t1
INNER JOIN mimiciii.icustays t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.admittime <= t2.intime
AND t1.dischtime >= t2.outtime
ORDER BY t1.HADM_ID, t2.ICUSTAY_ID ASC;

-- returns 60237 rows
SELECT t1.HADM_ID, t2.ICUSTAY_ID
FROM mimiciii.admissions t1
INNER JOIN mimiciii.icustays t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.admittime <= t2.intime
AND CAST(t2.OUTTIME AS DATE)- CAST(t1.DISCHTime AS DATE) < 1
ORDER BY t1.HADM_ID, t2.ICUSTAY_ID ASC;

