MySQL nested, nested subquery not getting outter variable

Question

I have a spendings table and a dates table, that are joined by date_id and id...

What I'm trying to do, is get from 1 query all the info from spendings, plus the sum of all the spendings but with a limit and/or offset

This is the query right now

SELECT spendings.id, spendings.price, spendings.title, 
       dates.date, users.username, currencies.value,
       ( SELECT SUM(sum_table.price) 
         FROM (
             SELECT s.price
             FROM spendings s, dates d
             WHERE s.date_id = d.id 
               AND day(d.date) = 25
             LIMIT 2 OFFSET 0
         ) as sum_table
       ) AS sum_price
FROM spendings, dates, users, currencies
WHERE spendings.date_id = dates.id 
  AND day(dates.date) = 25 
  AND spendings.user_id = users.id 
  AND 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, but only if the date here day(d.date) = 25 is the same as the outer one here day(dates.date) = 25

If instead I put day(d.date) = day(dates.date) which seems the logic thing to do, I get #1054 - Unknown column 'dates.date' in 'where clause'

If anyone has an idea to make this simpler let me know :)

What is a purpose of `LIMIT 2 OFFSET 0` in the subquery ? Do you want to sum only 2 random records ? — krokodilko, Apr 20 '14 at 05:18
This subquery makes not any sense to me, without an `order by` clause it just returns two random rows from the table, why don't use simple and faster `rand()` function instead ? And this is also a source of your problem, since you need two nested subqueries here because of this `limit X`. In SQL, when there are multiple nested subqueries, only the direct child (the subquery nested at level 1) sees column names of it's parent, but further subqueries cannot see the query at level x-2. I am afraid, You cannot do it in this query. — krokodilko, Apr 20 '14 at 11:13
@kordirko i want the limit because i want the users to be able to view spendings from different months, days, years etc but with limit on how many they can see on a page, in my example is set to 2 so i can see it better... and have the sum from those results... — C Alex, Apr 20 '14 at 11:29

score 1 · Accepted Answer · answered Apr 20 '14 at 12:00

Try to join instead of using nested correlated subqueries:

SELECT spendings.id, spendings.price, spendings.title, 
      dates.date, users.username, currencies.value,
      y.sum_price
FROM spendings, dates, users, currencies
JOIN (
    SELECT day, SUM(sum_table.price) As sum_price
    FROM (
        SELECT day(d.date) As day,
               s.price
        FROM spendings s, dates d
        WHERE s.date_id = d.id 
          AND day(d.date) = 25
        LIMIT 2 OFFSET 0
    ) sum_table
    GROUP BY day
) y
ON y.day = day(dates.date)
WHERE spendings.date_id = dates.id 
  -- AND day(dates.date) = 25 <== commented since it's redundant now
  AND spendings.user_id = users.id 
  AND spendings.curr_id = currencies.id

Some remarks:

Using old join syntax with commas is not recommended: FROM table1,table2,table2 WHERE
The recommended way of expressing joins is "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 this "new syntax" is quite old now, since is has been published, as I remember, in 1992 - 22 years ago. In IT industry 22 years is like 22 ages.

Im getting #1054 - Unknown column 'dates.date' in 'on clause' — C Alex, Apr 20 '14 at 12:23
Try to change the order of tables in the from clase in such a way in that `dates` table is directly before JOIN cluse ==> `FROM spendings, users, currencies, dates JOIN ( .....`. — krokodilko, Apr 20 '14 at 13:21
It works, the only change was to add the same limit to the outer select and now it works like a charm. Thank you very much! — C Alex, Apr 20 '14 at 14:31

MySQL nested, nested subquery not getting outter variable

1 Answers1