September 28, 2011 at 2:41 pm
This is puzzling...at least to me.
SELECT
A FROM TABLE 1
WHERE A NOT IN
(
SELECT Field1 FROM B
)
AND A IN
(
SELECT Field1 FROM C
)
The second subquery
(SELECT Field1 FROM C)
is invalid. Field1 doesn't exist in table C. Invalid column name Field1. It does not run when run on its own, but when I run the entire query it returns results.
I'd like to better understand why this happens....
September 28, 2011 at 3:13 pm
It's the same as any other correlated subquery. The processor first checks for the field in the scope of the subquery. If it can't find the field there, it uses the field from the outer query. So, your query is equivalent to
SELECT
A FROM
WHERE A NOT IN
(
SELECT Field1 FROM B
)
AND A IN
(
SELECT
.Field1 FROM C
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 29, 2011 at 4:40 am
SELECT Field1 FROM C
The Field1 is Table1's column
subquery can use outer table's column
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply