How to use results of subquery in a parent query case statement

Question

I have the following query that is returning the expected results:

SELECT
    locations.*,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 1
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_delivery_id,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 2
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_pickup_id
FROM
    locations

I would like to use the results of the subqueries (last_delivery_id, last_pickup_id) in a case statement in the parent query but I'm getting an error :

ERROR: Unknown column 'last_delivery_id'

SELECT
    locations.*, 
    case when last_delivery_id = 1 then 'pending' when last_delivery_id = 2 then 'active' end as status,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 1
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_delivery_id,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 2
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_pickup_id
FROM
    locations

Use your 1st query as a subquery and then you can select and use the 2 derived columns. — forpas, Dec 24 '20 at 17:51

score 1 · Accepted Answer · answered Dec 24 '20 at 17:54

You can use a subquery or CTE:

SELECT lh.*, . . .   -- any expressions you want
       (case when last_delivery_id = 1 then 'pending'
             when last_delivery_id = 2 then 'active'
        end) as status
FROM (SELECT l.*,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.h.created_at DESC
              LIMIT 1
             ) AS last_delivery_id,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 2 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS last_pickup_id
      FROM locations l
     ) lh

Note the use of table aliases which make the query easier to write and to read. That said, I'm not sure you really need the delivery_id if you just want the string version:

      SELECT l.*,
             (SELECT (case when h.id = 1 then 'pending'
                           when h.id = 2 then 'active'
                      end)
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS status
      FROM locations l

Thanks. This is the solution I ended up using. – Brian Kidd Dec 27 '20 at 21:01 — Brian Kidd, Dec 27 '20 at 21:01

score 1 · Answer 2 · answered Dec 24 '20 at 19:17

Consider re-writing the inline subqueries with a CTE and window function. This avoids the use of LIMIT clauses which can have performance issues if not run optimally. This requires MySQL version 8.0+ which supports CTEs and window functions:

WITH sub AS (
  SELECT id AS last_delivery_id
       , location_id
       , haul_type_id
       , ROW_NUMBER() OVER (PARTITION BY location_id, haul_type_id
                            ORDER BY created_at DESC) AS rn
  FROM hauls
)

SELECT l.*       -- CONSIDER EXPLICITLY SELECTING COLUMNS FOR PERFORMANCE
     , CASE
          WHEN h1.last_delivery_id = 1 THEN 'pending' 
          WHEN h2.last_delivery_id = 2 THEN 'active' 
       END AS `status`
FROM locations l
LEFT JOIN sub h1
  ON h1.location_id = l.id
 AND h1.haul_type_id = 1
 AND h1.rn = 1
LEFT JOIN sub h2
  ON h2.location_id = l.id
 AND h2.haul_type_id = 2
 AND h2.rn = 1

For earlier versions (<= MySQL 5.7), integrate an aggregate query:

SELECT l.*       -- CONSIDER EXPLICITLY SELECTING COLUMNS FOR PERFORMANCE 
     , CASE
          WHEN h1.id = 1 THEN 'pending' 
          WHEN h2.id = 2 THEN 'active' 
       END AS `status`
FROM locations l
LEFT JOIN
    (SELECT location_id
          , MAX(CASE WHEN haul_type_id = 1 THEN created_at END) AS h1_max_date
          , MAX(CASE WHEN haul_type_id = 2 THEN created_at END) AS h2_max_date
     FROM hauls
     GROUP BY location_id
    ) h_agg
   ON l.location_id = h_agg.location_id
LEFT JOIN hauls h1
   ON  h1.location_id = h_agg.location_id
   AND h1.haul_type_id = 1
   AND h1.created_at = h_agg.h1_max_date
LEFT JOIN hauls h2
   ON  h2.location_id = h_agg.location_id
   AND h2.haul_type_id = 2
   AND h2.created_at = h_agg.h2_max_date

Thanks. I ended up using another solution but appreciate you pointing out possible performance issues. I'll keep this solution nearby if I run into performance problems. — Brian Kidd, Dec 27 '20 at 21:02

score 1 · Answer 3 · answered Dec 24 '20 at 19:19

If you are running MySQL 8.0.14 or higher, you can use lateral joins for this:

select l.*, h1.*, h2.*
    case 
        when last_delivery_id = 1 then 'pending' 
        when last_delivery_id = 2 then 'active' 
    end as status
from locations l
left join lateral (
    select h.id as last_delivery_id
    from hauls h
    where h.haul_type_id = 1 and h.location_id = l.id
    order by h.created_at desc limit 1
) h1 on true
left join lateral (
    select h.id as last_pickup_id
    from hauls h
    where h.haul_type_id = 2 and h.location_id = l.id
    order by h.created_at desc limit 1
) h2 on true

Lateral joins are a powerful feature, that MySQL has been missing for long. The above statement, for example, can easily be adapted to return more columns from the each subquery.

Thanks. I like this but unfortunately cannot use MySQL 8. Thanks for the suggestion — Brian Kidd, Dec 27 '20 at 21:00

score 1 · Answer 4 · answered Dec 24 '20 at 19:49

1

because all the columns in select part execute all at once and last_delivery_id column dose not created yet and you try to use it , sql server return an error and for solve it you can use CTE , view and Drive table.

answered Dec 24 '20 at 19:49

MOUSA Rezvankhah

66
1

How to use results of subquery in a parent query case statement

4 Answers4