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
– krokodilko, Commented Apr 20, 2014 at 5: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
– krokodilko, Commented Apr 20, 2014 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
– C Alex, Commented Apr 20, 2014 at 11:29

krokodilko · Accepted Answer · 2014-04-20 12:00:08Z

1

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.

answered Apr 20, 2014 at 12:00

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

C Alex Over a year ago

Im getting #1054 - Unknown column 'dates.date' in 'on clause'

krokodilko Over a year ago

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 ( ......

C Alex Over a year ago

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!

Collectives™ on Stack Overflow

MySQL nested, nested subquery not getting outter variable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related