Reuse of a field from a joined table inside a subquery in FROM clause

Question

The following query is not the one I'm using really, but my problem can be replicated in this simpler one. Basically, what I want to do is use or reference a field from a joined table inside a subquery that is in a FROM clause like so.

SELECT t1.field1, t1.field2 - IIF(t3.calcfield is null, 0, t3.calc)
FROM
  (SELECT t2.fieldid, SUM(t2.field3) AS fsum
   FROM t2
   WHERE t2.date > t4.date 
   GROUP BY t2.fieldid) t3
LEFT JOIN
  table4 t4 ON t3.fieldid = t4.fieldid

The field that I need to use is t4.date to make the date comparison inside the subquery but when I execute the query I get the popup window or prompt to enter a value in t4.date.

I just wanted to make that comparison for filtering, so that I can use fsum field in the outer SELECT statement. I think the query is pretty simple to understand in terms of what I want to achieve.

Even though my query is more complex, I replicated the problem with a simple query like this or even simpler.

I tried replicating it in an AQL sandbox webpage: https://sqltest.net/. Tables generated by the sandbox, and in the upper right corner I selected the Oracle database.

Tables:

CREATE TABLE sql_test_a 
( 
    ID         VARCHAR2(4000 BYTE), 
    FIRST_NAME VARCHAR2(200 BYTE), 
    LAST_NAME  VARCHAR2(200 BYTE) 
); 

CREATE TABLE sql_test_b 
( 
    ID         VARCHAR2(4000 BYTE) 
); 

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'John', 'Snow'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('2', 'Mike', 'Tyson'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('3', 'Bill', 'Keaton'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('4', 'Greg', 'Mercury'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('5', 'Steve', 'Jobs'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('6', 'Johhny', 'Depp');

The SQL code I wrote is the following:

SELECT T1.ID, TINNER.FIRST_NAME
FROM 
  (SELECT T2.ID FROM sql_test_b T2
   WHERE T1.FIRST_NAME LIKE 'greg') TINNER
INNER JOIN
  sql_test_a T1 ON t1.ID = TINNER.ID;

I practically get the same error or problem that consists in that the T1 fields did not exist for the inner subquery or TINNER table.

I'm using Microsoft Access 2003. (simply cause I had it previously).

Answer 1

Recall SQL's logical order of operations that differ from its lexical order (i.e., order in how it is written). Usually the first step in query processing is the FROM clause, then JOIN, ON, WHERE, GROUP BY, etc. and usually ending with ORDER BY and SELECT (ironically one of the last clauses processed though written first).

Technically, your queries do not involve correlated subqueries since there are no inner or outer levels. Specifically, the derived table t3 and base table t4 are at the same level. The query engine evaluates t3 in isolation by itself during FROM clause step. Then, it evaluates JOIN table, t4, in isolation by itself and finally applies the matching ON logic.

Because t4 is not defined in the universe of t3, MS Access via GUI prompts for that parameter value (where MS Access via ODBC will raise an error). To resolve you have to include all necessary data sources in each table scope:

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM
  (SELECT t2.fieldid, SUM(t2.field3) AS fsum
   FROM t2
   INNER JOIN table4 sub_t4
     ON t2.fieldid = sub_t4.fieldid
   WHERE t2.date > sub_t4.date 
   GROUP BY t2.fieldid
  ) t3
LEFT JOIN table4 t4 
  ON t3.fieldid = t4.fieldid

---

Often, too, using layered queries is beneficial in Access and can help with final, compact queries:

t3 query (save below as a query object)

SELECT t2.fieldid, SUM(t2.field3) AS fsum
FROM t2
INNER JOIN table4 sub_t4
   ON t2.fieldid = sub_t4.fieldid
WHERE t2.date > sub_t4.date 
GROUP BY t2.fieldid

Final query (join saved query)

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM my_saved_query t3
LEFT JOIN table4 t4 
   ON t3.fieldid = t4.fieldid