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)
 Strange result set in left outer join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sasan.kh
Starting Member

23 Posts

Posted - 01/07/2013 :  10:54:19  Show Profile  Reply with Quote
Hi All
I have a table named Table1 with two integer fields: ID, ID2:

ID ID2
1 NULL
2 NULL
3 1
I have a view of this table named ViewOfTable1:

SELECT ID1, ID2, 'Ok' AS Flag
FROM Table1
WHERE ID2 IS NOT NULL
I run the following query:

SELECT Table1.ID, ViewOfTable1.Flag
FROM Table1
LEFT OUTER JOIN ViewOfTable1 ON Table1.ID = ViewOfTable1.ID2
I expect the result to be :

ID Flag
1 Ok
2 NULL
3 NULL
And this is what I get on my machine with SQL Server Desktop Edition installed but when I run this query in another machine with SQL Server 2000 Enterprise Edition SP4 I get:

ID Flag
1 Ok
2 OK
3 OK
What could be the problem?

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2013 :  11:27:28  Show Profile  Reply with Quote
check if definition of view is same in other machine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/07/2013 :  11:50:55  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
And try recompiling it.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/08/2013 :  08:25:33  Show Profile  Reply with Quote
the main problem is why 3 'Ok's are returned. There should be just one
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  09:09:49  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Could be that the views are diffrent or the data is different.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/08/2013 :  11:53:32  Show Profile  Reply with Quote
Thanx for your reply...
I am sure about the Data and the View, I can run some queries and post the results if it helps...
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  12:09:45  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
What does
select * from ViewOfTable1
return on each server

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/08/2013 :  14:03:23  Show Profile  Reply with Quote
on server 1:

Select * from Table1
ID1 ID2
1 null
2 null
3 1
----------------------
SELECT *
FROM dbo.ViewOfTable1

ID1 ID2 Flag
3 1 Ok
----------------------
SELECT *
FROM dbo.Table1 LEFT OUTER JOIN dbo.ViewOfTable1 ON dbo.Table1.ID1 = dbo.ViewOfTable1.ID2

ID1 ID2 ID1 ID2 Flag
1 null 3 1 Ok
2 null null null Ok
3 1 null null Ok








Edited by - sasan.kh on 01/08/2013 14:05:07
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/08/2013 :  14:17:53  Show Profile  Reply with Quote
On server 2:

-------------------------
SELECT *
FROM dbo.Table1
ID1 ID2
1 null
2 null
3 1

------------------------
SELECT *
FROM dbo.ViewOfTable1

ID1 ID2 Flag
3 1 Ok

------------------------
SELECT *
FROM dbo.Table1 LEFT OUTER JOIN dbo.ViewOfTable1 ON dbo.Table1.ID1 = dbo.ViewOfTable1.ID2

ID1 ID2 ID1 ID2 Flag
1 null 3 1 Ok
2 null null null null
3 1 null null null




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/08/2013 :  23:35:54  Show Profile  Reply with Quote
i think issue is in definition of view ViewOfTable1 . can you post definition from both servers?

you can get it by

sp_helptext 'ViewofTable1'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/09/2013 :  00:58:57  Show Profile  Reply with Quote
server 1:

CREATE VIEW dbo.ViewOfTable1
AS
SELECT ID1, ID2, 'Ok' AS Flag
FROM dbo.Table1
WHERE (ID2 IS NOT NULL)




server 2:

CREATE VIEW dbo.ViewOfTable1
AS
SELECT ID1, ID2, 'Ok' AS Flag
FROM dbo.Table1
WHERE (ID2 IS NOT NULL)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  01:30:42  Show Profile  Reply with Quote
whats ANSI NULL setting on both servers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/09/2013 :  03:48:54  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I don't see how you can get a row (null, null, 'ok') from the view.
You might look at the query plans to see if it doing something odd.
Did you recompile the view?
Try this on both servers

create table #a (id int, id2 int)
insert #a select 1, null
insert #a select 2, null
insert #a select 3, 1

select *
from #a a
left join (select *, c='ok' from #a where id2 is not null) b
on a.id = b.id2


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/09/2013 :  07:51:56  Show Profile  Reply with Quote
Your code returned:

1 NULL 3 1 ok
2 NULL NULL NULL NULL
3 1 NULL NULL NULL


as I expected but I can not understand the difference with my query...

quote:
Originally posted by nigelrivett



create table #a (id int, id2 int)
insert #a select 1, null
insert #a select 2, null
insert #a select 3, 1

select *
from #a a
left join (select *, c='ok' from #a where id2 is not null) b
on a.id = b.id2


Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/09/2013 :  08:02:53  Show Profile  Reply with Quote
When I change the query to :

select *
from table1 a
left join (select *, c='ok' from table1 where id2 is not null) b
on a.id1 = b.id2

I get :

1 NULL 3 1 ok
2 NULL NULL NULL NULL
3 1 NULL NULL NULL


but after running:

select *
from table1 a
left join viewOfTable1 b
on a.id1 = b.id2

when ViewOfTable1 is exactly equal to :
(select *, c='ok' from table1 where id2 is not null)

the result is :

1 NULL 3 1 ok
2 NULL NULL NULL ok
3 1 NULL NULL ok

Edited by - sasan.kh on 01/09/2013 08:05:49
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/09/2013 :  08:09:04  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

whats ANSI NULL setting on both servers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





It is OFF in both
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/09/2013 :  09:39:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
did you recompile the view?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sasan.kh
Starting Member

23 Posts

Posted - 01/09/2013 :  18:02:04  Show Profile  Reply with Quote
I didn't recompile it...
I will try to recompile and test
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.11 seconds. Powered By: Snitz Forums 2000