CODEWITHSUNDEEP
mysqlcase-when
joell
joell

Reputation: 71

MySql SELECT, check if record has different value in Status then use one

I have this table of orders and statuses:

id   |   date          |   status
123    2015-12-01           order
123    2015-12-20           refund
234    2015-12-21           order
456    2015-12-22           order
456    2015-12-22           refund

Now I'd like to get results on 'status' column that whenever the ID also has a status of 'refund' then use this value instead of value 'order'.

As follows: 

id   |   date        |   status    | refund_date
123    2015-12-01         refund      2015-12-20
234    2015-12-21         order
456    2015-12-22         refund      2015-12-22

I have this query that retrieves the refund_date but I'm unsure how to get the status for refund orders. Additionally the table has like 500k records so performance is a key.

select 
    id,
    date,
    status,
    case when count(id)>1 then max(date) else min(date) end as refund_date
from orders
group by id

Much appreciated.

Upvotes: 0

Views: 1590

Answers (2)

Willis Blackburn
Willis Blackburn

Reputation: 8204

Join an "order" view of the table with a "refund" view of the table. Use coalesce, which is a function that evaluates to the first non-null argument, to identify the status.

select t1.id, t1.date, coalesce(t2.status, t1.status), t2.date 
from orders t1 left outer join t2 on (t1.id = t2.id and t2.status = 'refund')
where t1.status = 'order'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

One method is to use union all for this purpose:

select o.*
from orders o
where o.status = 'refund'
union all
select o.*
from orders o
where o.status = 'order' and
      not exists (select 1
                  from orders o2
                  where o2.id = o.id and o2.status = 'refund'
                 );

With indexes on orders(status) and orders(id, status), this query should be quite fast.

Upvotes: 1

Related Questions