LEFT OUTER JOIN Problem - SQL Server Forums

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)

LEFT OUTER JOIN Problem

Author

Topic

BigGuy
Starting Member

3 Posts

Posted - 2009-03-14 : 12:40:48

Hello Gurus,

I have a very basic query that I am trying to convert from SQL Server 7.0 to SQL Server 2005 as *= does not work on SQL Server 2005.

Original query from SQL Server 7.0
-----------------------------------------

SELECT Users.uid as "User Name",
"Indexed Policies" = count(sod.sodlvl2id),
"Avg Time per Policy" = avg(sod.elapsedtime)
FROM T1 as sod (NOLOCK),
P1 as Policy (NOLOCK),
U1 as Users (NOLOCK)
WHERE sod.startdate between '01/01/2001' and '01/01/2003'
AND Policy.sodlvl2id *= sod.sodlvl2id
AND Users.Uid *= sod.Uid
Group By Users.Uid

It returns 120 rows.

I have checked the number of records in all the three tables in both the database they are exactly the same in SQL Server 7.0 and SQL Server 2005 databases.

Converted query in SQL Server 2005
----------------------------------

SELECT Users.uid as "User Name",
"Indexed Policies" = count(sod.sodlvl2id),
"Avg Time per Policy" = avg(sod.elapsedtime)
FROM T1 as sod (NOLOCK)
RIGHT OUTER JOIN P1 as Policy (NOLOCK)
ON (Policy.sodlvl2id = sod.sodlvl2id)
RIGHT OUTER JOIN U1 as Users (NOLOCK)
ON Users.Uid = sod.Uid
WHERE sod.startdate between '01/01/2001' and '01/01/2003'
Group By Users.Uid

It returns 28 rows.

Can any one let me know what I am doing wrong here.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 12:48:20

Do not use that join syntax. It's not valid in 2008. Take this opportunity to do away with it.

Mike
"oh, that monkey is going to pay"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 12:52:42

[code]SELECT Users.uid as "User Name",
"Indexed Policies" = count(sod.sodlvl2id),
"Avg Time per Policy" = avg(sod.elapsedtime)
FROM T1 as sod (NOLOCK)
LEFT OUTER JOIN P1 as Policy (NOLOCK)
ON (Policy.sodlvl2id = sod.sodlvl2id)
LEFT OUTER JOIN U1 as Users (NOLOCK)
ON Users.Uid = sod.Uid
AND sod.startdate between '01/01/2001' and '01/01/2003'
Group By Users.Uid[/code]

BigGuy
Starting Member

3 Posts

Posted - 2009-03-22 : 14:20:05

Hi Sodeep,

Thanks you very much. I will test the query tomorrow and get back to you. Thanks for your time.

Sanjay

quote:

Originally posted by sodeep

SELECT Users.uid as "User Name",
"Indexed Policies" = count(sod.sodlvl2id),
"Avg Time per Policy" = avg(sod.elapsedtime)
FROM T1 as sod (NOLOCK)
LEFT OUTER JOIN P1 as Policy (NOLOCK)
ON (Policy.sodlvl2id = sod.sodlvl2id)
LEFT OUTER JOIN U1 as Users (NOLOCK)
ON Users.Uid = sod.Uid
AND sod.startdate between '01/01/2001' and '01/01/2003'
Group By Users.Uid

BigGuy
Starting Member

3 Posts

Posted - 2009-03-22 : 14:22:02

Hi mfemenel,

I want to do it right, but if I can not use Join then what is the other way to right my query. If you can point in to the direction I really appreciate it.

Thanks
Sanjay

quote:
Originally posted by mfemenel

Do not use that join syntax. It's not valid in 2008. Take this opportunity to do away with it.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources