MYSQL - LEAST condition with case

Question

I have 2 tables: orders, orders_others

In orders, I have 2 important columns: priority_date, date_end

In orders_others, I have 1 important column: date_to

I want search next order (with 2 tables) but next date must be >= CURRENT DATE

MySQL CODE:

SELECT 
    users.*, 
    LEAST(
        MIN(
            CASE WHEN orders.date_end >= CURDATE() THEN orders.date_end ELSE "9999-99-99 99:99:99" END
        ), 
        MIN(
            CASE WHEN orders_others.date_to >= CURDATE() THEN orders_others.date_to ELSE "9999-99-99 99:99:99" END
        ), 
        MIN(
            CASE WHEN orders.priority_date >= CURDATE() THEN orders.priority_date ELSE "9999-99-99 99:99:99" END
        )
    ) as next_order, 
    MIN(
        CASE WHEN orders.date_end >= CURDATE() THEN orders.date_end ELSE "9999-99-99 99:99:99" END
    ) AS next_ending, 
    MIN(
        CASE WHEN orders_others.date_to >= CURDATE() THEN orders_others.date_to ELSE "9999-99-99 99:99:99" END
    ) AS next_order_others, 
    MIN(
        CASE WHEN orders.priority_date >= CURDATE() THEN orders.priority_date ELSE "9999-99-99 99:99:99" END
    ) as next_priority_date
FROM 
    `users` 
    LEFT JOIN `orders` ON `orders`.`idu` = `users`.`id` 
    LEFT JOIN `orders_others` ON `orders_others`.`unique` = `orders`.`unique` 
    AND `orders_others`.`date_to` >= (
        CURDATE() - INTERVAL 1 YEAR
    ) 
WHERE `next_order`>='2017-12-30 00:00:00'
GROUP BY 
    `users`.`id` 
ORDER BY 
    `next_order` ASC 
LIMIT 
    10

Result: Unknown column 'next_order' in 'where clause'

If I delete the condition it works fine :)

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 29 '17 at 10:29

Prateik Darji · Accepted Answer · 2017-12-29T10:49:40.403

the variable cannot directly assign to where condition and where should be just after FROM so for that, you can provide the condition in having clause.

SELECT 
users.*, 
LEAST(
    MIN(
        CASE WHEN orders.date_end >= CURDATE() THEN orders.date_end ELSE "9999-99-99 99:99:99" END
    ), 
    MIN(
        CASE WHEN orders_others.date_to >= CURDATE() THEN orders_others.date_to ELSE "9999-99-99 99:99:99" END
    ), 
    MIN(
        CASE WHEN orders.priority_date >= CURDATE() THEN orders.priority_date ELSE "9999-99-99 99:99:99" END
    )
) as next_order, 
MIN(
    CASE WHEN orders.date_end >= CURDATE() THEN orders.date_end ELSE "9999-99-99 99:99:99" END
) AS next_ending, 
MIN(
    CASE WHEN orders_others.date_to >= CURDATE() THEN orders_others.date_to ELSE "9999-99-99 99:99:99" END
) AS next_order_others, 
MIN(
    CASE WHEN orders.priority_date >= CURDATE() THEN orders.priority_date ELSE "9999-99-99 99:99:99" END
) as next_priority_date
FROM 
`users` 
LEFT JOIN `orders` ON `orders`.`idu` = `users`.`id` 
LEFT JOIN `orders_others` ON `orders_others`.`unique` = `orders`.`unique` 
AND `orders_others`.`date_to` >= (
    CURDATE() - INTERVAL 1 YEAR
) 

GROUP BY 
`users`.`id` having `next_order` >= '2017-12-30 00:00:00' 
ORDER BY 
`next_order` ASC 
LIMIT 10

yeah actually where is not needed here, i have updated my answer @cdaiga thanx for suggestion — Prateik Darji, Dec 29 '17 at 10:48

MYSQL - LEAST condition with case

1 Answers1