How to use an outer table in a nested two-level subquery?

Question

My query is like following:

SELECT codes.id, (SELECT SUM(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM a.end_date), EXTRACT(YEAR_MONTH FROM a.start_date))) months
  FROM (
    SELECT MIN(g.start_date) start_date, MAX(g.end_date) end_date 
      FROM (
        SELECT @group_id := @group_id + (@end_date IS NULL OR o.start_date > @end_date) group_id,
               start_date,
               @end_date := DATE(CASE 
                 WHEN (@end_date IS NULL OR o.start_date > @end_date) THEN o.end_date
                 ELSE GREATEST(o.end_date, @end_date)
               END) end_date  
          FROM dates_range o
          JOIN (SELECT @group_id := 0, @end_date := NULL) init
          WHERE o.code_id = codes.id -- THIS CLAUSE DOES NOT WORK 
      ORDER BY o.start_date ASC  
            ) g
  GROUP BY  g.group_id  
        ) a) as sum_date_ranges
FROM codes
-- A LOT OF JOINs and WHEREs OF codes.id THAT I CAN NOT MOVE INSIDE THE NESTED TWO LEVEL SUBQUERY

As it is written in the comments, that WHERE clause doesn't work, but I need to use codes.id inside that nested two-level subquery. How can I do it?

MySQL error:

Unknown column 'codes.id' in 'where clause'

You might be better off, rather than giving us something that doesn't work, a set of tables data and output requirements so we can write something that does. Most of SO is about fixing someone's broken code, but the sql guys tend to just write entire queries for people — Caius Jard, Mar 23 '19 at 07:28
@CaiusJard thanks for your suggestion! I should say that most of query is correct, because I took it from here: https://stackoverflow.com/questions/33498277/summing-range-of-dates-without-counting-overlaps-in-mysql The only problem is that line, my question is: is there an easy way to use an outer table in a nested two-level subquery like I would do in my query, or it's totally impossible? — Bob, Mar 23 '19 at 07:40
This code has a lot of problems. For instance, it is using the same variable in multiple expressions. MySQL does not guarantee the order of evaluation of expressions in `SELECT`. The best solution is to simplify the problem a bit and ask a *new* question. Provide sample data, desired results, your simplified code attempt, and an explanation of what the code is trying to do. — Gordon Linoff, Mar 23 '19 at 12:03

Solarflare · Accepted Answer · 2019-03-23T09:11:42.780

It is not possible to access an outer table from a derived table (simplifying a bit, this is any "subquery" that requires an alias, in your case g and a).

You have to do the required operation on the outer level with a join. For that, you need to keep track of the o.code_id and pass it through to the outer level:

SELECT codes.id, sum_date_ranges.sum_date_ranges
FROM codes
JOIN 
  (SELECT a.code_id, SUM( ... ) as sum_date_ranges
    (SELECT g.code_id, g.group_id, MIN( ... ), MAX( ... )
     FROM ( SELECT o.code_id, @group_id = ...
            FROM dates_range o ...
            -- WHERE o.code_id = codes.id -- not required
            ORDER BY o.code_id, o.start_date
          ) g
     GROUP BY g.code_id, g.group_id
    ) a
    GROUP BY a.code_id
  ) as sum_date_ranges
ON sum_date_ranges.code_id = codes.id
-- the rest of your joins and where-conditions

You may need to adept the @group_id = ... to restart at 0 for every new o.code_id, but since you do not seem to be using the absolute value anywhere, it probably doesn't matter.

This will evaluate the complete derived table for every code_id and then throw away everything it doesn't need (which may or may not be a significant part of your data). To prevent that, you can instead actually put the outer conditions inside the query:

SELECT sum_date_ranges.code_id as id, sum_date_ranges.sum_date_ranges
-- from codes -- not required anymore, we get codes.id from derived table
FROM 
  (SELECT a.code_id, SUM( ... ) as sum_date_ranges
    (SELECT g.code_id, g.group_id, MIN( ... ), MAX( ... )
     FROM ( SELECT o.code_id, @group_id = ...
            FROM dates_range o ...
            WHERE o.code_id IN (SELECT codes.id 
                                FROM codes
                                -- your join and where-conditions
                             )
            ORDER BY o.code_id, o.start_date
          ) g
     GROUP BY g.code_id, g.group_id
  ) a
  GROUP BY a.code_id
) as sum_date_ranges
-- optionally in case you need other columns from codes
-- JOIN codes ON codes.id = sum_date_ranges.code_id

This assumes you actually only need the column codes.id from codes (and that your joins do not multiply your rows), but your query might have been simplified in that regard, so you can of course still join codes (and other tables) again to get the columns you need (but you do not need a where-condition here anymore).

It might be possible to completely rewrite your query without a derived table, but it would probably require a lot of modifications and is unlikely to work with variables. It's probably easiest if you start from scratch (and if you need help with that, you would need to provide the complete query, sample data, expected output and some explanation what your code is supposed to do). Specifically, if you use MySQL 8, you can probably use window functions instead of variables to get that kind of ranking, sums, first and last values, although it looks like you still would need a derived table after all.

How to use an outer table in a nested two-level subquery?

1 Answers1