CODEWITHSUNDEEP
mysqlsql
ErikL
ErikL

Reputation: 2041

mysql: reference main query in subquery

I have 2 tables: Orders and order_line

In orders I have data like:

id | name   | order_date
1  | order 1| 2014-01-01
2  | order 2| 2015-01-01
3  | order 3| 2016-01-01

In orderline there is data like:

id | Order_id | order_status |status_date
1  | 1        | New          | 2014-01-01
2  | 1        | Shipped      | 2014-10-01
3  | 2        | New          | 2015-01-01
4  | 2        | Canceled     | 2015-03-01
5  | 3        | New          | 2016-01-01
6  | 3        | Canceled     | 2016-02-01

a orderline does have a date and a status

Now I want to know how many lines had a Given status status in a given month/year. So for example, I would expect a value of 1 for status "New" on 2014-08, and a value of 2 for "cancelled" on 2016-03. To report that, I need to somehow get the latest order_line row for every order, and report on that. If I have just a single order, I can get the latest orderline like this:

select orderline.* from orders o 
join (select * from orderline
      where order_id =  1
      and status_date = (select max(status_date) 
                        from orderline 
                        where order_id = 1
                        and status_date < MAKEDATE(2018, 8))) as orderline on orderline.order_id = o.id 
where o.id = 1;

I would somehow need to link the sub query to the parent query I guess, but I can't figure out how to do this. When I try to replace the 1 in the sub query with "where order_id = o.id" I get Error Code: 1054. Unknown column 'o.id' in 'where clause'

code to create / fill the sample tables:

  CREATE TABLE orders
    (`id` int(11), `name` varchar(7), `order_date` datetime)
;

INSERT INTO orders
    (`id`, `name`, `order_date`)
VALUES
    (1, 'order 1', '2014-01-01 00:00:00'),
    (2, 'order 2', '2015-01-01 00:00:00'),
    (3, 'order 3', '2016-01-01 00:00:00')
;



CREATE TABLE orderline
    (`id` int, `Order_id` int, `order_status` varchar(8), `status_date` datetime)
;

INSERT INTO orderline
    (`id`, `Order_id`, `order_status`, `status_date`)
VALUES
    (1, 1, 'New', '2014-01-01 00:00:00'),
    (2, 1, 'Shipped', '2014-10-01 00:00:00'),
    (3, 2, 'New', '2015-01-01 00:00:00'),
    (4, 2, 'Canceled', '2015-03-01 00:00:00'),
    (5, 3, 'New', '2016-01-01 00:00:00'),
    (6, 3, 'Canceled', '2016-02-01 00:00:00')
;

Upvotes: 1

Views: 364

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think you want something like this:

select ol.order_status, count(*)
from orderline ol
where ol.status_date = (select max(ol2.status_date)
                        from orderline ol
                        where ol2.order_id = ol.order_id and
                              ol2.status_date < '2014-08-01'
                       )
group by ol.order_status;

This returns the number of order lines with a given status as of a particular date (the date in the subquery).

Upvotes: 2

Related Questions