Re: Strange SQL results

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

Date: Fri, 7 May 2004 15:04:22 -0400

Here is DDL + INSERT's + queries:

declare @t1 table
(
 id int null
   unique
)

declare @t2 table
(
 id int null
   unique
)

insert @t1 values (1)
insert @t1 values (2)
insert @t1 values (3)
insert @t1 values (null)

insert @t2 values (1)
insert @t2 values (2)
insert @t2 values (null)

-- Mine
select
 *
from
 @t1 t1
where not exists
(
 select
  *
 from
  @t2 t2
 where
  t2.id = t1.id
 or (t1.id is null and t2.id is null)
)

-- Yours
select
 id
from
 @t1
where id not in
(
 select
  id
 from
  @t1
 where id in
 (
  select
   id
  from
   @t2
 )
)

Notice how I'm allowing nulls for id, since I don't have your DDL. I get
the same results as you do. If you could please post sample data that
reproduces the problem, we can see why you have to resort to the query you
have. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marin Kostadinovic" <mkostadinovic@yahoo.com> wrote in message
news:FE7BB80A-7E42-4C69-A39D-A49D7D8028F4@microsoft.com...
I did not tried Join query, but I got proper data set by running following
statement (whose sub query returns EXACTLY same dataset):
Select TagId
>From Tag
Where TagId Not In
(
   Select T.TagId From
      Tag T
   Where T.TagId In
   (
      Select KR.[Id]
      From t_Keep_Rec KR
   )
)
This is resolution for my problem, I just want to know why this is happening
and, even more important, what is a PROPER way to create this kind of script
to get reliable results.
Thank you, Marin

Relevant Pages

  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Re: Need assistance with query
    ... for and the query that you are looking for is a complicated query to write ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • variable substitution vs. executing dynamic strings
    ... WHY is the second query so much ... (This is actually an sproc where the start / end variables are passed_ The ... declare @start int ...
    (microsoft.public.sqlserver)
  • Re: Is this an MSSQL bug?
    ... > I have the following query on MSSQL 2000. ... > declare @t table(i int identity, j int, k int) ... > An explicit value for the identity column in table '@t' can only be ...
    (microsoft.public.sqlserver.programming)
  • Re: adding values into a drop down list
    ... int count = 5; ... that number into the ddl. ... For example if the query result is 9, then I will add item 1 to 9 into ... I usded to use this line to add, but I have to specify the value.. ...
    (microsoft.public.dotnet.framework.aspnet)