Issue

This Content is from Stack Overflow. Question asked by Fatty Low

Please refer 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

I have bold the sub-query and that is my 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?

Solution

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

This Question was asked in StackOverflow by Fatty Low and Answered by GRIV It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.