ScottPletcher (10/9/2015)
A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local table alias to make sure you are referencing a column in the local table.Edit: That is:
select s.* from #Security s where s.[Security Id] in (select distinct sms.[Security Id] from #SystemMappedSecurity sms)
Then you'll get the error you expect, and can correct it.
Thanks for explaining this in such simple words. Now I will do the same for my fellow colleagues. 🙂