Question 20848 in Mysql Question posted in
The official MySQL documentation can be found here.

Create a status column with results based on another table using a case statement – Mysql

I have 2 tables

orders

order_id
1
2
3

orders_details

order_id sku ordered received
1 abc 10 10
1 xyz 10 10
2 abc 10 6
2 xyz 10 0
3 abc 10 0
4 xyz 10 0

I would like to add to the orders table a column called status which would have the results ‘complete’, ‘partial’ and ‘open’ based on what has been received in the orders_details table

result I am trying to acheive

order_id status
1 complete
2 partial
3 open

The problem is when a different product from the same order_id has a different received result it creates an additional row with the group by.

result I am getting

order_id status
1 complete
2 open
2 partial
3 open

https://www.db-fiddle.com/f/bFrYguhmcMJ32iFuUcXfDw/3

SELECT 
    `orders`.`order_id`,
  (CASE
   WHEN `received` >= `ordered` THEN 'complete'
   WHEN `received` < `ordered` AND `received` != 0 THEN 'partial'
   WHEN `received` = 0 THEN 'open'
   END) AS `status`
 FROM
    `orders`
LEFT JOIN `orders_details` ON `orders_details`.`order_id` = `orders`.`order_id`
GROUP BY 
    `order_id`, `status`
;
Tags:

2

Answers


  1. 0

    It is a bit hacky, but you can define the order by giving then names of the status an order the should be choosen, and then take the maximum for this order.

    Zcomplete>partial>open

    We can thenremove the additional charatcers

    Query #1

    SELECT 
    `orders`.`order_id`,
  REPLACE (MAX((CASE
   WHEN `received` >= `ordered` THEN 'Zcomplete'
   WHEN `received` < `ordered` AND `received` != 0 THEN 'partial'
   WHEN `received` = 0 THEN 'open'
   END)), 'Z', '') AS `status`
 FROM
    `orders`
LEFT JOIN `orders_details` ON `orders_details`.`order_id` = `orders`.`order_id`
GROUP BY 
    `order_id`
;
    order_id status
    1 complete
    2 partial
    3 open

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    I might be missing something, but I think you just want:

    SELECT `orders`.`order_id`,
    CASE SUM(received)
        WHEN 0 THEN 'open'
        WHEN SUM(ordered) THEN 'complete'
        ELSE 'partial'
    END AS `status`
FROM `orders`
LEFT JOIN `orders_details`
    ON `orders_details`.`order_id` = `orders`.`order_id`
GROUP BY `order_id`;

    Here’s your updated DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search