Use subquery result in parent query case statement

Question

I have a hauls table that has columns id location_id completed_at and others. I would like to add a column to the query results that has the days since the previous completed haul. This query returns the expected results:

SELECT
    `h`.*,
    (
        SELECT
            `completed_at`
        FROM
            `hauls`
        WHERE
            `location_id` = `h`.`location_id`
            AND `completed_at` < `h`.`completed_at`
        ORDER BY
            `completed_at` DESC
        LIMIT 1) AS `previous_completed_at`
FROM
    hauls h

But I would like to use DATEDIFF(h.completed_at, previous_completed_at) as days in the parent query select statement but previous_completed_at cannot be found.

I realize I could use a lateral join but I need this to be MySQL 5.7 compatible.

Is there a way to accomplish this?

In mysql 8.0, you could use the window LAG() function. – ypercubeᵀᴹ Sep 01 '21 at 12:13 — ypercubeᵀᴹ, Sep 01 '21 at 12:13

score 1 · Accepted Answer · answered Sep 01 '21 at 04:13

One way to do this would be to wrap the entire query as a subquery:

SELECT tmp.*,
       DATEDIFF(tmp.`completed_at`, tmp.`previous_completed_at`) as `days`
  FROM (SELECT h.*,
               (SELECT z.`completed_at`
                  FROM `hauls` z
                 WHERE z.`location_id` = h.`location_id` AND z.`completed_at` < h.`completed_at`
                 ORDER BY z.`completed_at` DESC
                 LIMIT 1) AS `previous_completed_at`
          FROM `hauls` h) tmp 
 ORDER BY tmp.`completed_at` DESC;

The * is suboptimal, but this is one way to write the query so that it is compatible with every version of MySQL.

Be sure to add some WHERE clauses to these queries so that system performance does not suffer

Use subquery result in parent query case statement

1 Answers1