select - MySQL nested, nested subquery not getting outter variable -

i have spendings table , dates table, joined date_id , id...

what i'm trying do, 1 query info spendings, plus sum of spendings limit and/or offset

this query right now

select spendings.id, spendings.price, spendings.title,         dates.date, users.username, currencies.value,        ( select sum(sum_table.price)           (              select s.price              spendings s, dates d              s.date_id = d.id                 , day(d.date) = 25              limit 2 offset 0          ) sum_table        ) sum_price spendings, dates, users, currencies spendings.date_id = dates.id    , day(dates.date) = 25    , spendings.user_id = users.id    , spendings.curr_id = currencies.id limit 2 offset 0 

output

id  price   title   date       username value  sum_price 3   6.00    title1  2013-11-25 alex     €      21.00 4   15.00   title2  2013-11-25 alex     €      21.00 

it works, if date here day(d.date) = 25 same outer 1 here day(dates.date) = 25

if instead put day(d.date) = day(dates.date) seems logic thing do, #1054 - unknown column 'dates.date' in 'where clause'

if has idea make simpler let me know :)

try join instead of using nested correlated subqueries:

select spendings.id, spendings.price, spendings.title,        dates.date, users.username, currencies.value,       y.sum_price spendings, dates, users, currencies join (     select day, sum(sum_table.price) sum_price     (         select day(d.date) day,                s.price         spendings s, dates d         s.date_id = d.id            , day(d.date) = 25         limit 2 offset 0     ) sum_table     group day ) y on y.day = day(dates.date) spendings.date_id = dates.id    -- , day(dates.date) = 25 <== commented since it's redundant   , spendings.user_id = users.id    , spendings.curr_id = currencies.id 

---

some remarks:

---

using old join syntax commas not recommended: from table1,table2,table2 where
recommended way of expressing joins "new" ansi sql join syntax:

from table1 [left|right|cross|[full] outer|natural] join table2 {on|using} join_condition1 [left|right|cross|[full] outer|natural] join table3 {on|using} join_condition2 .... 

actually "new syntax" quite old now, since has been published, remember, in 1992 - 22 years ago. in industry 22 years 22 ages.

---