how to pass the parent's value into sub-query or sub sub-query-CodePudding

Please refer to the example query below::

    SELECT
    a.id,
    a.status,
    a.updated_at,
    b.id,
    b.updated_at,
    IF((a.status = 1 OR a.status = 2), 
   (IF(a.status = 1), 
         (SELECT COUNT(*) FROM coconut c WHERE c.datetime BETWEEN a.updated_at AND b.updated_at) , 
         (SELECT COUNT(*) FROM coconut c WHERE c.datetime BETWEEN a.updated_at AND NOW()) ), 0) AS 'ab'
    FROM apple a
    LEFT JOIN banana b

The sub-queries inside IF condition are the issue that gave me error message: "unknown column a.updated_at" and "unknown column b.updated_at" when I'm trying to get the data from table apple and table banana.

Is there any other way to pass the value from table apple and table banana into the sub-query?

CodePudding user response：

You can run your counts through a CASE statement (essentially a IF-THEN-ELSE statement), which is more flexible than the IF function because it allows more than one branch (read more here about it from a previous question). Also, don't forget to include your JOIN conditions after LEFT JOIN.

SELECT
a.id,
a.status,
a.updated_at,
b.id,
b.updated_at,
CASE WHEN (a.status = 1) THEN
     (SELECT COUNT(*) FROM coconut c WHERE c.datetime BETWEEN a.updated_at AND b.updated_at)
     WHEN (a.status = 2) THEN 
     (SELECT COUNT(*) FROM coconut c WHERE c.datetime BETWEEN a.updated_at AND NOW())
     ELSE 0
END AS 'ab'
FROM apple a
LEFT JOIN banana b ON a.id = b.id