User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a subquery. In this subquery I need to generate a number by doing a lookup on the first table and modifying the result. More specifically, I have a table A (id, date), and table B (id, weekday) and I need to return a date for each row of B where it is the *next date occurring on the same weekday where such a date does not occur in A*, all starting from a specific minimum date, where entries in table A are all higher (ie, later) than that date. Table B is sorted by staggered weekday (First Monday, First Tuesday... Second Monday, Second Thursday..) and then these get "slotted in" (by assigning "date") where that date in A does not occur. So if the two tables look something like: A id | date a-a 2014-11-11 a-b 2014-11-13 a-c 2014-11-18 B id | weekday b-a Tuesday b-b Tuesday b-c Wednesday Then the resulting sort (if start date is 2014-11-10, a Monday) would be: sorted id | date a-a 2014-11-11 b-c 2014-11-12 (Wednesday) a-b 2014-11-13 a-c 2014-11-18 b-a 2014-11-25 (Tuesday) b-b 2014-12-02 (Tuesday) I have been working on this query for a while now, and almost had it done, but then I couldn't find a way to pass the "start date" to the subquery that allocates dates to table B. The docs say that > Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query. So I reimplemented the subquery to move the variable to a WHERE clause, using JOINs, but this still didn't seem to help, since I couldn't figure out how to return just the single row needed without an outer query to do that. So here is what I have, in the hopes someone can help me figure this out: SELECT @next_date as next_date, @tmp_i := CASE w.weekday + 0 WHEN 0 THEN @idxw_U:=@idxw_U+1 WHEN 1 THEN @idxw_M:=@idxw_M+1 WHEN 2 THEN @idxw_T:=@idxw_T+1 WHEN 3 THEN @idxw_W:=@idxw_W+1 WHEN 4 THEN @idxw_R:=@idxw_R+1 WHEN 5 THEN @idxw_F:=@idxw_F+1 WHEN 6 THEN @idxw_S:=@idxw_S+1 END as idxW, @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset, @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr, w.weekday as weekday, @dayofweek:= w.weekday+0 as dweekday, @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date, @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date), IFNULL( ( SELECT next_slot FROM ( SELECT -- DAYOFWEEK(d.date) as weekdaynum, -- dayname(d.date) as weekday, -- d.date, -- MIN(d2.date) as min_d2, DATE_ADD(MIN(d2.date), INTERVAL 1 WEEK) as next_slot, -- DATEDIFF(d2.date, d.date) as datediff, -- DATEDIFF(d2.date, d.date) DIV 7 as weeksdiff, -- DATEDIFF(MIN(d3.date), d2.date) DIV 7 as nextdiff FROM t_date as d JOIN t_date as d2 JOIN t_date as d3 WHERE d.date >= @TMP_date and DAYOFWEEK(@TMP_date) = DAYOFWEEK(d.date) = DAYOFWEEK(d2.date) = DAYOFWEEK(d3.date) and d2.date > d.date and d3.date > d2.date and DATEDIFF(d2.date, d.date) = 7 GROUP BY d.date ORDER BY d.date ASC ) as t -- these 3 lines below are the ones I need to figure out how to accomplish without an outer query -- ie, find the first (lowest date) result that has a nextdiff greater than 1 (a gap of more than 1 week between results) WHERE nextdiff >1 ORDER BY date ASC LIMIT 1 ) , CONCAT('dow: ',@dayofweek,' ',@TMP_date) ) -- , 1) -- commented this out while debugging, debug output is line above; in final, if return is null, that means offset is one week , 0 ) as clash_offset, @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date, w.extra FROM t_weekday as w JOIN (SELECT @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0, @next_date := DATE_ADD((SELECT t.date FROM t as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) ) as varrs ORDER BY date DESC, weekday, id ASC @TMP_date is the starting (minimum) date. The subquery is the one inside the IFNULL, and tested outside of this, as its own query, it works perfectly, when specifying a literal value (or setting the variable in a JOIN()). As a test, I did: SELECT @next_date as next_date, @tmp_i := CASE w.weekday + 0 WHEN 0 THEN @idxw_U:=@idxw_U+1 WHEN 1 THEN @idxw_M:=@idxw_M+1 WHEN 2 THEN @idxw_T:=@idxw_T+1 WHEN 3 THEN @idxw_W:=@idxw_W+1 WHEN 4 THEN @idxw_R:=@idxw_R+1 WHEN 5 THEN @idxw_F:=@idxw_F+1 WHEN 6 THEN @idxw_S:=@idxw_S+1 END as idxW, @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset, @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr, w.weekday as weekday, @dayofweek:= w.weekday+0 as dweekday, @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date, @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date), IFNULL(:= IF((SELECT 1 FROM t_date WHERE show_after = @TMP_show_after), IFNULL( ( -- SELECT f FROM ( -- uncommenting this line and the ftmp one results in NULL, with them commented out, @TMP_date is picked up no problem SELECT @TMP_date as f LIMIT 1 -- ) as ftmp ) , CONCAT('dow: ',@dayofweek,' ',@TMP_show_after) ) -- , 1) , 0 ) as clash_offset, @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date, w.extra FROM t_weekday as w JOIN (SELECT @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0, @next_date := DATE_ADD((SELECT t.date FROM t as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) ) as varrs ORDER BY date DESC, weekday, id ASC Which confirms what the docs say. However, I don't know how to modify my query (the first one) to get the result I want. ---------- Edit: I found an error in the logic, I need to return the highest result (date) in a series of *nextdiff*s if all return 1, or the first higher-than-1 result. It currently only would return the latter. So that needs fixing too.