MySQL: Referencing subquery columns

Question

I'm currently working on some reporting for an eCommerce system that needs to show how long an order has been delayed. I have a log of projected delivery dates for the order and am able to get the initial and last dates, but doing more complex things like comparing the projected dates is proving problematic.

A (simplified version of) my query is as follows:

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection
FROM orders
-- and some other joins here for additional report data

(Just FYI, I'm sorting on IDs rather than dates here because dispatch projections can move forward as well as back, for example if a stock shipment comes in ahead of schedule).

This is fine for extracting some raw data about the projected delivery history for an order, but I want to do some additional analysis of the data, for example how many days difference there is between the initial projected shipping date, the final projected shipping date and the actual shipping date. This is where I run into trouble.

I tried adding a DATEDIFF(final_delivery_projection, initial_delivery_projection) column to my SELECT clause in order to see how many days back a given delivery prediction has slipped, but MySQL wouldn't have it.

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection,
       DATEDIFF(final_delivery_projection - initial_delivery_projection) AS projection_days_revised
FROM orders
-- and some other joins here for additional report data

Unknown column final_delivery_projection in field list

Presumably you can't use an alias in a select statement if the alias is referring to a subselect in the same statement.

I would also like to be able to exclude orders in the WHERE clause based on the results of the projections. For example, I'd like to exclude all orders where the final projected shipping date falls before the initial projected date on the grounds that I'm only interested in orders that are being held up rather than ones that have shipped ahead of schedule.

Is extracting and processing the data I'm trying to get in the same statement possible, or will I have to do some post-processing in the client to work things like this out? If it is possible in SQL, then how can it be done?

Is the syntax for DATEDIFF correct? DATEDIFF ( datepart , startdate , enddate ) is what I use. — Matt
– Matt, Commented Apr 29, 2016 at 12:25
I'm not getting any syntax errors on DATEDIFF and the documentation says it only takes two arguments — GordonM
– GordonM, Commented Apr 29, 2016 at 12:27

xQbert · Accepted Answer · 2016-04-29 12:34:12Z

1

I believe the values for the select are generated at the same time. So date diff may not be able to be computed since the aliased value may not be known at the time. If you refactor the Select, you may be able to get the results you're looking for. Additionally this would give you access to additional table values which you may want for additional analysis on sub queries/data sets.

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       First.Projected_Date AS initial_delivery_projection,
       Current.Projected_Data AS final_delivery_projection,
       dateDiff(Current.Projected_date,First.Projected_date)
FROM orders O
LEFT JOIN (SELECT projected_date, Order_ID 
           FROM order_delivery_projections
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1) First
    ON First.order_id = O.order_id
LEFT JOIN (SELECT projected_date, Order_ID
           FROM order_delivery_projections
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1)
    ON Current.Order_ID = O.Order_ID

Is it possible that an Order_ID in Orders may not exist in order_Delivery_projections? If so then date diff on a null value may cause errors as well...

answered Apr 29, 2016 at 12:34

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GordonM Over a year ago

This does work, but it is also really slow. I'll accept if there's no better solution

xQbert Over a year ago

It really shouldn't be slow... are indexes on Orders.OrderID and order_Delivery_projections.orderID and order_Delivery_Projection_ID?

GordonM Over a year ago

I think the issue is the ORDER BY ASC to get the first record and ORDER BY DESC on the same table to get the last. My suspicion is that it's causing MySQL to do something brain-dead. There's no performance issues if I do one or the other but doing both seems to kill it.

GordonM Over a year ago

I've accepted this because it appears to be correct in theory. In practice, however, I've not been able to overcome the issues this approach has. In the end it turns out that just adding a subselect to the SELECT portion of the query every time you need the data is actually quite a bit faster (by half) so it's worth living with the messy query that results. I'll have to put it down to MySQL's query optimiser not being very optimal.

GordonM Over a year ago

Actually, after further review I'm withdrawing the accept on the grounds that the limit clause can potentially cause no rows at all to be matched (if the first one the engine happens to grab doesn't have the right order ID) so the query doesn't provide the right results. I'm posting a query that works shortly.

Community · Accepted Answer · 2017-05-23 12:31:44Z

0

Having a look around you need to used derived tables in order to access the alias name in the select query. see here from more info

try this query:

SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       initial_delivery_projection,
       final_delivery_projection,
       DATEDIFF(final_delivery_projection - initial_delivery_projection) AS projection_days_revised
FROM (
    SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection
   FROM orders) z

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 29, 2016 at 12:37

Matt

1,7573 gold badges13 silver badges26 bronze badges

2 Comments

GordonM Over a year ago

This doesn't work because MySQL says orders.order_id is an unknown column. I don't think you can use outer query references in the inner query

Matt Over a year ago

Have you tried removing the orders. part on either select/both selects? Just a guess. If not good luck as im not sure without messing around on an example table :)

GordonM · Accepted Answer · 2016-05-03 12:51:13Z

I spent a lot of time researching this issue, and the problem is a fundamental issue with MySQL that means you can't refer to an outer query in an inner query if the inner query is in the FROM portion of the query, even though you can refer to the outer query if the inner query is in the SELECT portion.

As for getting the first and last records from the delivery date history, I've discovered this query which produces the correct results with acceptable performance.

SELECT odr.order_id,
    first_change.projected_date AS initial_projected_dispatch_date,
    last_change.projected_date AS final_projected_dispatch_date
FROM order AS odr
LEFT JOIN (
    SELECT 
        order_id, 
        MIN(order_delivery_projection_id) AS first_id, 
        MAX(order_delivery_projection_id) AS last_id
    FROM order_delivery_projections
    GROUP BY order_id
) AS change_record_finder ON change_record_finder.order_id = odr.order_id
LEFT JOIN order_delivery_projections AS first_change ON first_change.order_delivery_projection_id = change_record_finder.first_id
LEFT JOIN order_delivery_projections AS last_change ON last_change.order_delivery_projection_id = change_record_finder.last_id
WHERE -- where clauses go here

Matt's solution would have worked in other SQL flavours, but MySQL lacks the required functionality to allow his otherwise correct answer to work.

Collectives™ on Stack Overflow

MySQL: Referencing subquery columns

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related