No rows returned in my query -- why?

No rows returned in my query -- why?

Post by John Peterso » Sat, 23 Nov 2002 17:51:10



(SQL Server 2000, SP2)

Hello, all!

I'm sure that there's a simple explanation for why I'm not getting any rows
returned in my query, but it must be a Friday, because I can't seem to get
my brain around it and I was hoping you could help.  :-)

Consider:

use tempdb
go

create table Main (MainId int not NULL, [Name] varchar(255) not NULL)
go

insert into Main values (1, 'Test1')
insert into Main values (2, 'Test2')
go

create table A (AId int not NULL, Message ntext not NULL)
go

create table B (BId int not NULL, MainId int not NULL, AId int not NULL)
go

create view BwithA
as
  select b.BId,
         b.MainId,
         b.AId,
         a.Message
    from      B as b
   inner join A as a on a.AId = b.AId
go

select m.*,
       b.Message
  from      Main   as m
  left join BwithA as b on b.MainId = m.MainId
go

This is all well and good, and seems to work as expected.

However, if I add a WHERE clause to my SELECT statement that starts to
reference BwithA.Message, I don't get any rows returned.  For example, if I
change the query to:

select m.*,
       b.Message
  from      Main   as m
  left join BwithA as b on b.MainId = m.MainId
 where b.Message is NULL

Then no rows are returned.  I'm not sure I understand *why*.  If someone can
explain it to me, I'd be very grateful!  :-)

John Peterson

 
 
 

No rows returned in my query -- why?

Post by harvinde » Sat, 23 Nov 2002 18:16:26


try :

select m.*,
       b.Message
  from      Main   as m
  left join BwithA as b on b.MainId = m.MainId
 and b.Message is NULL

Quote:>-----Original Message-----
>(SQL Server 2000, SP2)

>Hello, all!

>I'm sure that there's a simple explanation for why I'm

not getting any rows
Quote:>returned in my query, but it must be a Friday, because I
can't seem to get
>my brain around it and I was hoping you could help.  :-)

>Consider:

>use tempdb
>go

>create table Main (MainId int not NULL, [Name] varchar
(255) not NULL)
>go

>insert into Main values (1, 'Test1')
>insert into Main values (2, 'Test2')
>go

>create table A (AId int not NULL, Message ntext not NULL)
>go

>create table B (BId int not NULL, MainId int not NULL,
AId int not NULL)
>go

>create view BwithA
>as
>  select b.BId,
>         b.MainId,
>         b.AId,
>         a.Message
>    from      B as b
>   inner join A as a on a.AId = b.AId
>go

>select m.*,
>       b.Message
>  from      Main   as m
>  left join BwithA as b on b.MainId = m.MainId
>go

>This is all well and good, and seems to work as expected.

>However, if I add a WHERE clause to my SELECT statement
that starts to
>reference BwithA.Message, I don't get any rows returned.  
For example, if I
>change the query to:

>select m.*,
>       b.Message
>  from      Main   as m
>  left join BwithA as b on b.MainId = m.MainId
> where b.Message is NULL

>Then no rows are returned.  I'm not sure I understand

*why*.  If someone can

- Show quoted text -

Quote:>explain it to me, I'd be very grateful!  :-)

>John Peterson

>.


 
 
 

No rows returned in my query -- why?

Post by John Peterso » Sat, 23 Nov 2002 18:29:54


Hello, harvinder!

Ah...that seems to work.  But why is that any different than putting it in
the WHERE clause?

Also, curiously, if I *explicitly* convert the field in the WHERE clause
(even to the same datatype), then it works, too:

select m.*,
       b.Message
  from      Main   as m
  left join BwithA as b on b.MainId = m.MainId
 where convert(ntext, b.Message) is NULL

I'm kind of confused on this issue...


Quote:> try :

> select m.*,
>        b.Message
>   from      Main   as m
>   left join BwithA as b on b.MainId = m.MainId
>  and b.Message is NULL

> >-----Original Message-----
> >(SQL Server 2000, SP2)

> >Hello, all!

> >I'm sure that there's a simple explanation for why I'm
> not getting any rows
> >returned in my query, but it must be a Friday, because I
> can't seem to get
> >my brain around it and I was hoping you could help.  :-)

> >Consider:

> >use tempdb
> >go

> >create table Main (MainId int not NULL, [Name] varchar
> (255) not NULL)
> >go

> >insert into Main values (1, 'Test1')
> >insert into Main values (2, 'Test2')
> >go

> >create table A (AId int not NULL, Message ntext not NULL)
> >go

> >create table B (BId int not NULL, MainId int not NULL,
> AId int not NULL)
> >go

> >create view BwithA
> >as
> >  select b.BId,
> >         b.MainId,
> >         b.AId,
> >         a.Message
> >    from      B as b
> >   inner join A as a on a.AId = b.AId
> >go

> >select m.*,
> >       b.Message
> >  from      Main   as m
> >  left join BwithA as b on b.MainId = m.MainId
> >go

> >This is all well and good, and seems to work as expected.

> >However, if I add a WHERE clause to my SELECT statement
> that starts to
> >reference BwithA.Message, I don't get any rows returned.
> For example, if I
> >change the query to:

> >select m.*,
> >       b.Message
> >  from      Main   as m
> >  left join BwithA as b on b.MainId = m.MainId
> > where b.Message is NULL

> >Then no rows are returned.  I'm not sure I understand
> *why*.  If someone can
> >explain it to me, I'd be very grateful!  :-)

> >John Peterson

> >.

 
 
 

No rows returned in my query -- why?

Post by harvinde » Sat, 23 Nov 2002 20:10:30


Hi John,

If we use AND clause it will evaluate AND along with left
join condition and if we use WHERE clause if*evaluate
the join condition and then try to filter for WHERE so no
rows returned.

Thanks
--Harvinder

>-----Original Message-----
>Hello, harvinder!

>Ah...that seems to work.  But why is that any different
than putting it in
>the WHERE clause?

>Also, curiously, if I *explicitly* convert the field in
the WHERE clause
>(even to the same datatype), then it works, too:

>select m.*,
>       b.Message
>  from      Main   as m
>  left join BwithA as b on b.MainId = m.MainId
> where convert(ntext, b.Message) is NULL

>I'm kind of confused on this issue...



>> try :

>> select m.*,
>>        b.Message
>>   from      Main   as m
>>   left join BwithA as b on b.MainId = m.MainId
>>  and b.Message is NULL

>> >-----Original Message-----
>> >(SQL Server 2000, SP2)

>> >Hello, all!

>> >I'm sure that there's a simple explanation for why I'm
>> not getting any rows
>> >returned in my query, but it must be a Friday, because
I
>> can't seem to get
>> >my brain around it and I was hoping you could help.  :-
)

>> >Consider:

>> >use tempdb
>> >go

>> >create table Main (MainId int not NULL, [Name] varchar
>> (255) not NULL)
>> >go

>> >insert into Main values (1, 'Test1')
>> >insert into Main values (2, 'Test2')
>> >go

>> >create table A (AId int not NULL, Message ntext not
NULL)
>> >go

>> >create table B (BId int not NULL, MainId int not NULL,
>> AId int not NULL)
>> >go

>> >create view BwithA
>> >as
>> >  select b.BId,
>> >         b.MainId,
>> >         b.AId,
>> >         a.Message
>> >    from      B as b
>> >   inner join A as a on a.AId = b.AId
>> >go

>> >select m.*,
>> >       b.Message
>> >  from      Main   as m
>> >  left join BwithA as b on b.MainId = m.MainId
>> >go

>> >This is all well and good, and seems to work as
expected.

>> >However, if I add a WHERE clause to my SELECT statement
>> that starts to
>> >reference BwithA.Message, I don't get any rows
returned.
>> For example, if I
>> >change the query to:

>> >select m.*,
>> >       b.Message
>> >  from      Main   as m
>> >  left join BwithA as b on b.MainId = m.MainId
>> > where b.Message is NULL

>> >Then no rows are returned.  I'm not sure I understand
>> *why*.  If someone can
>> >explain it to me, I'd be very grateful!  :-)

>> >John Peterson

>> >.

>.

 
 
 

No rows returned in my query -- why?

Post by John Peterso » Sat, 23 Nov 2002 21:55:12


But, if we use the WHERE, it *is* filtering on rows that should exist, no?
Particuarly when you compare that against using the CONVERT in the WHERE.

As I say...something is fishy here.  I wonder if this is a bug?


> Hi John,

> If we use AND clause it will evaluate AND along with left
> join condition and if we use WHERE clause if*evaluate
> the join condition and then try to filter for WHERE so no
> rows returned.

> Thanks
> --Harvinder

> >-----Original Message-----
> >Hello, harvinder!

> >Ah...that seems to work.  But why is that any different
> than putting it in
> >the WHERE clause?

> >Also, curiously, if I *explicitly* convert the field in
> the WHERE clause
> >(even to the same datatype), then it works, too:

> >select m.*,
> >       b.Message
> >  from      Main   as m
> >  left join BwithA as b on b.MainId = m.MainId
> > where convert(ntext, b.Message) is NULL

> >I'm kind of confused on this issue...



> >> try :

> >> select m.*,
> >>        b.Message
> >>   from      Main   as m
> >>   left join BwithA as b on b.MainId = m.MainId
> >>  and b.Message is NULL

> >> >-----Original Message-----
> >> >(SQL Server 2000, SP2)

> >> >Hello, all!

> >> >I'm sure that there's a simple explanation for why I'm
> >> not getting any rows
> >> >returned in my query, but it must be a Friday, because
> I
> >> can't seem to get
> >> >my brain around it and I was hoping you could help.  :-
> )

> >> >Consider:

> >> >use tempdb
> >> >go

> >> >create table Main (MainId int not NULL, [Name] varchar
> >> (255) not NULL)
> >> >go

> >> >insert into Main values (1, 'Test1')
> >> >insert into Main values (2, 'Test2')
> >> >go

> >> >create table A (AId int not NULL, Message ntext not
> NULL)
> >> >go

> >> >create table B (BId int not NULL, MainId int not NULL,
> >> AId int not NULL)
> >> >go

> >> >create view BwithA
> >> >as
> >> >  select b.BId,
> >> >         b.MainId,
> >> >         b.AId,
> >> >         a.Message
> >> >    from      B as b
> >> >   inner join A as a on a.AId = b.AId
> >> >go

> >> >select m.*,
> >> >       b.Message
> >> >  from      Main   as m
> >> >  left join BwithA as b on b.MainId = m.MainId
> >> >go

> >> >This is all well and good, and seems to work as
> expected.

> >> >However, if I add a WHERE clause to my SELECT statement
> >> that starts to
> >> >reference BwithA.Message, I don't get any rows
> returned.
> >> For example, if I
> >> >change the query to:

> >> >select m.*,
> >> >       b.Message
> >> >  from      Main   as m
> >> >  left join BwithA as b on b.MainId = m.MainId
> >> > where b.Message is NULL

> >> >Then no rows are returned.  I'm not sure I understand
> >> *why*.  If someone can
> >> >explain it to me, I'd be very grateful!  :-)

> >> >John Peterson

> >> >.

> >.

 
 
 

No rows returned in my query -- why?

Post by Tibor Karasz » Fri, 29 Nov 2002 14:20:06


Hi John!

I see your point here. To me, this is a bug in the optimizer (I think, I might be
misinterpreting the base table-view relations here). A view, according to the ANSI standard,
should behave like if you first materialize the data from the view and then run your query
against that materialized query. Here, it seems like SQL Server is pushing the WHERE clause into
the view definition (it actually flattens out the view), hence the need for restriction in the
FROM clause instead of the WHERE clause.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> (SQL Server 2000, SP2)

> Hello, all!

> I'm sure that there's a simple explanation for why I'm not getting any rows
> returned in my query, but it must be a Friday, because I can't seem to get
> my brain around it and I was hoping you could help.  :-)

> Consider:

> use tempdb
> go

> create table Main (MainId int not NULL, [Name] varchar(255) not NULL)
> go

> insert into Main values (1, 'Test1')
> insert into Main values (2, 'Test2')
> go

> create table A (AId int not NULL, Message ntext not NULL)
> go

> create table B (BId int not NULL, MainId int not NULL, AId int not NULL)
> go

> create view BwithA
> as
>   select b.BId,
>          b.MainId,
>          b.AId,
>          a.Message
>     from      B as b
>    inner join A as a on a.AId = b.AId
> go

> select m.*,
>        b.Message
>   from      Main   as m
>   left join BwithA as b on b.MainId = m.MainId
> go

> This is all well and good, and seems to work as expected.

> However, if I add a WHERE clause to my SELECT statement that starts to
> reference BwithA.Message, I don't get any rows returned.  For example, if I
> change the query to:

> select m.*,
>        b.Message
>   from      Main   as m
>   left join BwithA as b on b.MainId = m.MainId
>  where b.Message is NULL

> Then no rows are returned.  I'm not sure I understand *why*.  If someone can
> explain it to me, I'd be very grateful!  :-)

> John Peterson

 
 
 

No rows returned in my query -- why?

Post by John Peterso » Sat, 30 Nov 2002 15:30:50


Hello, Tibor!

What seems weird to me, is that if I do a CONVERT on the field in question
(to the *same* datatype), then it behaves as expected (I'll cut-n-paste from
a response to harvinder in this thread):

select m.*,
       b.Message
  from      Main   as m
  left join BwithA as b on b.MainId = m.MainId
 where convert(ntext, b.Message) is NULL

Thanks for the information regarding the ANSI "definition" of a VIEW.
That's good to keep in mind.  I *think* that the original SQL that I
presented is adhering to that definition, however, no?

Thanks again for your help!  :-)

John Peterson



Quote:> Hi John!

> I see your point here. To me, this is a bug in the optimizer (I think, I
might be
> misinterpreting the base table-view relations here). A view, according to
the ANSI standard,
> should behave like if you first materialize the data from the view and
then run your query
> against that materialized query. Here, it seems like SQL Server is pushing

the WHERE clause into
Quote:> the view definition (it actually flattens out the view), hence the need

for restriction in the
Quote:> FROM clause instead of the WHERE clause.

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




Quote:> > (SQL Server 2000, SP2)

> > Hello, all!

> > I'm sure that there's a simple explanation for why I'm not getting any
rows
> > returned in my query, but it must be a Friday, because I can't seem to
get
> > my brain around it and I was hoping you could help.  :-)

> > Consider:

> > use tempdb
> > go

> > create table Main (MainId int not NULL, [Name] varchar(255) not NULL)
> > go

> > insert into Main values (1, 'Test1')
> > insert into Main values (2, 'Test2')
> > go

> > create table A (AId int not NULL, Message ntext not NULL)
> > go

> > create table B (BId int not NULL, MainId int not NULL, AId int not NULL)
> > go

> > create view BwithA
> > as
> >   select b.BId,
> >          b.MainId,
> >          b.AId,
> >          a.Message
> >     from      B as b
> >    inner join A as a on a.AId = b.AId
> > go

> > select m.*,
> >        b.Message
> >   from      Main   as m
> >   left join BwithA as b on b.MainId = m.MainId
> > go

> > This is all well and good, and seems to work as expected.

> > However, if I add a WHERE clause to my SELECT statement that starts to
> > reference BwithA.Message, I don't get any rows returned.  For example,
if I
> > change the query to:

> > select m.*,
> >        b.Message
> >   from      Main   as m
> >   left join BwithA as b on b.MainId = m.MainId
> >  where b.Message is NULL

> > Then no rows are returned.  I'm not sure I understand *why*.  If someone
can
> > explain it to me, I'd be very grateful!  :-)

> > John Peterson

 
 
 

1. SELECT query in Access97 to SQL 6.5 returning wrong records - why Why WHY

Hi,
I am using an Access97 query with the following syntax

SELECT AR_Deliveries.DeliveryQuantity, AR_Deliveries.DeliveryDate,
AR_Deliveries.CompanyID, AR_Deliveries.CustomerID, AR_Deliveries.StockCode
FROM AR_Deliveries
WHERE (((AR_Deliveries.CompanyID)="A") AND
((AR_Deliveries.CustomerID)="DM003") AND
((AR_Deliveries.StockCode)="8XZ-923207-000"))
ORDER BY AR_Deliveries.DeliveryDate DESC;

I get 82 records out of a few thousand, they all come from customer DM003
but all different stock codes and quantities.

AR_Deliveries is an SQL Server view, which returns records from another SQL
Server database on the same server.  These records are correct, but
obviously include all records, not the subset I want.

I have suspected the ODBC drivers, and loaded the latest.  I currently have
the following versions.

    SQL ODBC Drivers - 3.60.0319
    SQL Server 6.5 sp 4

I have tested the Access database on a PC with ODBC ver 2.6 drivers
installed and get the same results.

why is this happening? if anybody has reun into the same problem please let
me know how you fixed it.
Thanks
Warren

2. Multi-User Database

3. Q328736

4. Why query always return one row???

5. Particular search

6. sql question for single row query returns more than one row

7. Backup Exec SQL Agent

8. Why Jet always returns one row?

9. StoredProc returns all rows of a table (WHY!)

10. Why does this query return no result set???

11. Ho do I return rows N through M from a query