Strange result set in left outer join

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)

Strange result set in left outer join

Author

Topic

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-07 : 10:54:19

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

52326 Posts

Posted - 2013-01-07 : 11:27:28

check if definition of view is same in other machine

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-07 : 11:50:55

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.

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-08 : 08:25:33

the main problem is why 3 'Ok's are returned. There should be just one

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 09:09:49

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.

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-08 : 11:53:32

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...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 12:09:45

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.

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-08 : 14:03:23

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

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-08 : 14:17:53

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 23:35:54

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/

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-09 : 00:58:57

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 01:30:42

whats ANSI NULL setting on both servers?

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-09 : 03:48:54

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.

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-09 : 07:51:56

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

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-09 : 08:02:53

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

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-09 : 08:09:04

quote:
Originally posted by visakh16

whats ANSI NULL setting on both servers?

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

It is OFF in both

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-09 : 09:39:24

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.

sasan.kh
Starting Member

23 Posts

Posted - 2013-01-09 : 18:02:04

I didn't recompile it...
I will try to recompile and test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources