SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 select query on SQL Server 7.0 and SQL Express
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ujjaval
Posting Yak Master

108 Posts

Posted - 11/25/2006 :  23:31:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/25/2006 :  23:46:27  Show Profile  Reply with Quote
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

Edited by - snSQL on 11/26/2006 15:45:45
Go to Top of Page

ujjaval
Posting Yak Master

108 Posts

Posted - 11/25/2006 :  23:54:23  Show Profile  Reply with Quote
Yes, I do get identical results. It happens when I include table TumourGuide in it. I can't understand that bit.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/26/2006 :  00:25:04  Show Profile  Reply with Quote
For both queries I gave you, not just the Episode one? How many rows do you get for the Episode one?

Edited by - snSQL on 11/26/2006 00:25:36
Go to Top of Page

ujjaval
Posting Yak Master

108 Posts

Posted - 11/26/2006 :  00:39:01  Show Profile  Reply with Quote
Oh sorry for that. I misunderstood.

With the episode query I get 2 results.

With the TumourGuide query I get 9 results.

-Ujjaval
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/26/2006 :  00:47:32  Show Profile  Reply with Quote
You'll need to post the structure of the two tables and the results of those two queries I gave you too.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/26/2006 :  02:56:17  Show Profile  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/26/2006 :  15:47:26  Show Profile  Reply with Quote
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.
Go to Top of Page

ujjaval
Posting Yak Master

108 Posts

Posted - 11/27/2006 :  01:47:21  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/27/2006 :  04:23:22  Show Profile  Reply with Quote
Glad you got it sorted out
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000