Error Code: 1242. Subquery returns more than 1 row in Attribute Subquery-CodePudding

I'm trying to get the difference between two dates deathtime and admittime using subquery in attribute list. This is the script that I run:

SELECT a.subject_id, a.hadm_id, a.admittime, a.deathtime, a.diagnosis, p.drug, 
(SELECT DATEDIFF(deathtime, admittime) FROM admissions WHERE deathtime <> '0000-00-00') AS diff FROM
    admissions AS a INNER JOIN prescriptions AS p
    ON a.subject_id = p.subject_id 
    WHERE diagnosis = 'CARDIAC ARREST';

It returns Error Code: 1242. Subquery returns more than 1 row when I run it. Would really appreciate any help.

CodePudding user response：

The reason why you're getting that error is because a subquery can't return more than 1 result.

Try this:

SELECT a.subject_id, a.hadm_id, a.admittime,
a.deathtime, a.diagnosis, p.drug, CASE WHEN a.deathtime <> '0000-00-00'THEN DATEDIFF(deathtime, admittime) AS Diff FROM admissions AS a INNER JOIN prescriptions AS p ON a.subject_id = p.subject_id WHERE diagnosis = 'CARDIAC ARREST';