Different Results in Translating 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 2005 Forums

Transact-SQL (2005)

Different Results in Translating Left Outer Join

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-07-10 : 12:09:13

I am translating SQL to get rid of '*=' and '=*'.

This query:

select
	* 
FROM	
	(select 1 ID) c,
	(select 2 ClaimID, case when 1=0 then 1 end AccountLocationID) b,
	(select 3 ID) l
WHERE	
	b.ClaimID =* c.ID AND 
	b.AccountLocationID =* l.ID

SQL Server Tranlates to:

SELECT   
  *
FROM         
  (SELECT     1 AS ID) AS c LEFT OUTER JOIN
  (SELECT     2 AS ClaimID, CASE WHEN 1 = 0 THEN 1 END AS AccountLocationID) AS b ON c.ID = b.ClaimID RIGHT OUTER JOIN
  (SELECT     3 AS ID) AS l ON b.AccountLocationID = l.ID

The result is different. Anyone know why? Or which is correct?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 12:19:21

The correct result would be the original query result.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 12:33:32

I think you need to post some more sample data.
At least four records per table.

SELECT		c.ID,
		b.ClaimID,
		b.AccountLocationID,
		l.ID
FROM		(
			SELECT	1 AS ID
		) AS c
CROSS JOIN	(
			SELECT	4 AS ID
		) AS l
LEFT JOIN	(
			SELECT	2 AS ClaimID,
				CASE
					WHEN 1 = 0 THEN 3
				END AS AccountLocationID
		) AS b ON b.ClaimID = c.ID
			AND b.AccountLocationID = l.ID

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources