2

Ok, maybe I'm a noob in sql, but I can't figure out why this should work:

I've Table1 like:

IDRecord (PK), Description, IDTable2 (FK)

and a Table2 like this:

IDRecord (PK), Description

with Table1.IDTable2 as FK to Table2.IDRecord.

Then I've a very simple query:

select * from Table1
where IDTable2 not in (select IDMispelledRecord from Table2)

I've made a syntax error! There's not a column called IDMispelledRecord in Table2, and if I execute the subquery alone, it returns to me 

Invalid column name 'IDMispelledRecord'.

But if I execute the WHOLE query it doesn't raise an error, simply returns 0 rows.

Can anyone tell me why?

tanathos
  • 5,566
  • 4
  • 34
  • 46

1 Answers1

2

Though your question doesn't show this (please post your actual code in future) Table1 must have a column called IDMispelledRecord.

Your subquery is referencing that column from the outer query.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • oh Lord, you're right. There's a field in the upper table with the same name I wrongly used in the subquery... – tanathos Oct 14 '11 at 10:17
  • 1
    @tanathos - [This can be particular dangerous in a `DELETE`!](http://stackoverflow.com/questions/4594733/sql-server-2008-management-studio-not-checking-the-syntax-of-my-query/4594765#4594765) – Martin Smith Oct 14 '11 at 10:19