select query on SQL Server 7.0 and SQL Express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select query on SQL Server 7.0 and SQL Express

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-11-25 : 23:31:39

Hello everyone,

I can not understand why this is happening like this..

I have a stored procedure on SQL Server 7.0 database which executes a select statement as shown below:


select e.UnivID, e.TumourID, e.Date_Diag, t.TumourName from [dbo].[Episode] AS e, [dbo].[TumourGuide] AS t
	where e.UnivID = 1 and e.TumourID = t.TumourID order by e.Date_Diag DESC

On SQL Server 7.0 database, this gives me correct answer as 2 results.

But I also installed same database on SQL Express server and I executed same query I get 8 results instead of 2.

Does anyone can tell if there is anything wrong with my query or there is some change/bug in SQL Express server?

Thanks,
Ujjaval

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-25 : 23:46:27

Do you get identical results on both servers if you run these two queries?

select e.UnivID, e.TumourID, e.Date_Diag
from [dbo].[Episode] AS e
where e.UnivID = 1
order by e.Date_Diag DESC

select t.TumourID, t.TumourName 
from [dbo].[TumourGuide] AS t
where 
order by TumourID

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-11-25 : 23:54:23

Yes, I do get identical results. It happens when I include table TumourGuide in it. I can't understand that bit.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-26 : 00:25:04

For both queries I gave you, not just the Episode one? How many rows do you get for the Episode one?

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-11-26 : 00:39:01

Oh sorry for that. I misunderstood.

With the episode query I get 2 results.

With the TumourGuide query I get 9 results.

-Ujjaval

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-26 : 00:47:32

You'll need to post the structure of the two tables and the results of those two queries I gave you too.

Kristen
Test

22859 Posts

Posted - 2006-11-26 : 02:56:17

How is that second query working at all?


select t.TumourID, t.TumourName 
from [dbo].[TumourGuide] AS t
where 
order by TumourID

I'm very curious as to how ujjaval's query could behave differently between the two versions of SQL Server

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-26 : 15:47:26

quote:
How is that second query working at all?

My mistake

I know - I'm pretty sure that there's just duplicate data, I don't think there is really any difference.

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-11-27 : 01:47:21

Apologies to everyone. Sorry Kristen and snSQL, for TumourGuide query, I was confident enough that I wrote the ideal number of rows without running that query. But that was the table screwing me up. I ran it and there it is. TumourGuide table had duplicate data. That's why I was getting it like that. I am such a stupid.

But thanks for that.

Ujjaval

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 04:23:22

Glad you got it sorted out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources