SUB QUERY driving me NUTS! 
Author Message
 SUB QUERY driving me NUTS!

Hey,

Can anyone help me?  

I KNOW results should be returned by the subquery below, however,
nothing is coming back and I cannot figure out why! The same query in
Access (with the same data) returns results.  Am I missing something
really stupid?   Is there something about SQL Server that I don't know?  

Thanks in advance,

Georgia Taylor

1. This returns 0 results:

SELECT old_id FROM investigators WHERE old_id = 1450

2. This returns 1 result:

SELECT old_pi FROM protocols WHERE old_pi = 1450

3. BUT, this returns 0 results

SELECT old_pi from protocols
WHERE old_pi NOT IN
        (select old_id from investigators)



Sun, 26 Dec 2004 23:36:30 GMT
 SUB QUERY driving me NUTS!

Post table definitions and data example otherwise this is really a wild
guess. What data type is old_id and old_pi? Maybe it's not numeric and
implicit conversion takes place. Or they are different data types and
conversion is taking place.


Quote:
> Hey,

> Can anyone help me?

> I KNOW results should be returned by the subquery below, however,
> nothing is coming back and I cannot figure out why! The same query in
> Access (with the same data) returns results.  Am I missing something
> really stupid?   Is there something about SQL Server that I don't know?

> Thanks in advance,

> Georgia Taylor

> 1. This returns 0 results:

> SELECT old_id FROM investigators WHERE old_id = 1450

> 2. This returns 1 result:

> SELECT old_pi FROM protocols WHERE old_pi = 1450

> 3. BUT, this returns 0 results

> SELECT old_pi from protocols
> WHERE old_pi NOT IN
> (select old_id from investigators)



Mon, 27 Dec 2004 02:20:43 GMT
 SUB QUERY driving me NUTS!
[posted and mailed, please reply in news]

Quote:

> 1. This returns 0 results:

> SELECT old_id FROM investigators WHERE old_id = 1450

> 2. This returns 1 result:

> SELECT old_pi FROM protocols WHERE old_pi = 1450

> 3. BUT, this returns 0 results

> SELECT old_pi from protocols
> WHERE old_pi NOT IN
>      (select old_id from investigators)

I presume that there are NULL values in investigators.old_id? Since
comparisons with NULL always result in UNKNOWN and never in TRUE,
you cannot get any rows back.

Use NOT EXISTS instead:

   SELECT old_pi
   FROM   protocols p
   WHERE  NOT EXISTS (SELECT *
                      FROM   investigators i
                      WHERE  i.old_id = p.old_id)

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Mon, 27 Dec 2004 05:48:57 GMT
 SUB QUERY driving me NUTS!
Cool, this worked.  I thought I had tried EXISTS, but clearly I didn't
do it correctly.

Muchos gracias.

Quote:

> [posted and mailed, please reply in news]


> > 1. This returns 0 results:

> > SELECT old_id FROM investigators WHERE old_id = 1450

> > 2. This returns 1 result:

> > SELECT old_pi FROM protocols WHERE old_pi = 1450

> > 3. BUT, this returns 0 results

> > SELECT old_pi from protocols
> > WHERE old_pi NOT IN
> >      (select old_id from investigators)

> I presume that there are NULL values in investigators.old_id? Since
> comparisons with NULL always result in UNKNOWN and never in TRUE,
> you cannot get any rows back.

> Use NOT EXISTS instead:

>    SELECT old_pi
>    FROM   protocols p
>    WHERE  NOT EXISTS (SELECT *
>                       FROM   investigators i
>                       WHERE  i.old_id = p.old_id)

> --
> Erland Sommarskog, SQL Server MVP

> Books Online (updated!) for SQL 2000 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Mon, 27 Dec 2004 19:20:05 GMT
 SUB QUERY driving me NUTS!
Note that using NOT IN with literals e.g. NOT IN (null,1450) would work with
ansi_nulls off. But it works differently with table (since it will do a
join).


Quote:
> Cool, this worked.  I thought I had tried EXISTS, but clearly I didn't
> do it correctly.

> Muchos gracias.


> > [posted and mailed, please reply in news]


> > > 1. This returns 0 results:

> > > SELECT old_id FROM investigators WHERE old_id = 1450

> > > 2. This returns 1 result:

> > > SELECT old_pi FROM protocols WHERE old_pi = 1450

> > > 3. BUT, this returns 0 results

> > > SELECT old_pi from protocols
> > > WHERE old_pi NOT IN
> > >      (select old_id from investigators)

> > I presume that there are NULL values in investigators.old_id? Since
> > comparisons with NULL always result in UNKNOWN and never in TRUE,
> > you cannot get any rows back.

> > Use NOT EXISTS instead:

> >    SELECT old_pi
> >    FROM   protocols p
> >    WHERE  NOT EXISTS (SELECT *
> >                       FROM   investigators i
> >                       WHERE  i.old_id = p.old_id)

> > --
> > Erland Sommarskog, SQL Server MVP

> > Books Online (updated!) for SQL 2000 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Mon, 27 Dec 2004 23:35:57 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. This query is driving me nuts - please help !!

2. Query using UDF joined to a sub query vs Temp Table to sub Query

3. DTS from ASP security driving me nuts!

4. Driving me nuts!!

5. SQL Error 2417 driving me nuts

6. Triggers drive me NUTS!

7. I'm sure it something simple but it's driving me nuts :-(

8. Synax problem driving me nuts

9. Sub queries <> sub tablesl

10. HELP: isql is driving me nuts!

11. Index Tuning Wizard Driving me NUTS

12. Please Help, this is driving me nuts !!


 
Powered by phpBB® Forum Software