Re: subquery problem, wrong reference in the subquery?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 07/09/04 

Date: Fri, 9 Jul 2004 14:02:21 -0400

There's nothing wrong with the query. What were you expecting? 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"MailSurfer" <microstone@hotmail.com> wrote in message
news:e96a3678.0407090958.5d672e8d@posting.google.com...
In the following example, the t2 table doesn't even have that keyID
field.  The query still runs and give out results.
Why? Is this a known bug?
/*
create table t1 (rowid int primary key, keyid int, rowname
varchar(100))
create table t2 (rowid int primary key, rowname varchar(100))
insert into t1 values (1,1,'a')
insert into t1 values (2,1,'b')
insert into t1 values (3,2,'c')
insert into t2 values (1,'d')
insert into t2 values (2,'e')
select * from t1 where keyid in (select keyid from t2)
drop table t1, t2
*/
ON
Microsoft SQL Server  7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Access to SQLServer GCE
    ... Actually, as Larry points out, the Access client does a good job of filtering only information that you need. ... If your query is bound to a report, and you open that report supplying a typical "where" for that report, then Access will NOT pull down all the records, but in most cases Access will only pull down the required records and respect your filter. ... So SQL server will respect the conditions and filtering placed into those queries, and therefore only pull down those records you require. ... The suggestion in these cases is to consider using a pass-through query since all of that summing is done before the row comes down the network pipe. ...
    (comp.databases.ms-access)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)