Problems with joins, it freezes

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 2005 Forums

Transact-SQL (2005)

Problems with joins, it freezes

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2008-03-05 : 12:02:33

Hi all

I have a master table, with ID Column int, and Description column Varchar

And another details table, with MasterID Int, and Detail id INT

If i use this query:

 SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col=b.id_col

Never returns data, and get freeze, and i have to kill process on Managment

But if i use this:

 SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col=b.id_col+0

It returns data i Want, is this a Bug? Or i have an error?
The 2 columns is the same data.

But if I run this,

 SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col+0=b.id_col

Nothing happens,

What could it be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-05 : 12:10:23

what are the datatypes of the id columns in both tables?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

rekiller
Starting Member

31 Posts

Posted - 2008-03-05 : 12:20:20

Both columns are INT

In Table1 is PK, and in Table2 is FK

I know this is rare, i have use SQL for years but i can not solve this problem.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 12:24:52

I've run into several occasions with 2005 where it seems to have some issue with a query, mostly when using derived tables. It's hard to say what the actual issue is. You might try updating statistics and/or clearing the caches. Another thing to explore are the estimated execution plans to see what SQL thinks before you execute the code. Then look into "Execution Plan Caching and Reuse" in BOL. That might help you diagnose the issue.

BTW, what size are the tables and are the ID columns indexed?

rekiller
Starting Member

31 Posts

Posted - 2008-03-05 : 12:55:41

They are Indexed,
Tables only has 10 rows each.

They are in development, not in production

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 02:24:17

Post your edition and version number of your server.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources