Query join syntax help - 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 2000 Forums

Transact-SQL (2000)

Query join syntax help

Author

Topic

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2003-05-14 : 12:40:00

O wise gurus,

I need to re-write a query to use the proper ANSI join syntax (i.e., usa a LEFT OUTER JOIN in the FROM clause instead of *= in the WHERE clause).

Here is the query:

SELECT COUNT(*)
FROM Store a, FranchEst b, Report_FWeek c, Franchisee d
WHERE a.iStoreId*=b.iStoreID
AND b.dtWeekEnd=*c.WeekEndingDate
AND a.iFranchiseeid=d.iFranchiseeid

Can anyone help me with the syntax on this?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 12:50:08



    SELECT COUNT(*) 
      FROM Store a
 LEFT JOIN FranchEst b
        ON a.iStoreId=b.iStoreID
 LEFT JOIN Report_FWeek c
        ON b.dtWeekEnd=c.WeekEndingDate 
INNER JOIN Franchisee d
        ON a.iFranchiseeid=d.iFranchiseeid

Is what you have, but I question the Inner Join to D.

Brett

8-)

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2003-05-14 : 14:54:00

Very interesting... but now I'm getting considerably more rows with the new query. I've had a couple other gurus take a look at it and they seem to think it might not be possible to represent this exact same query using ANSI joins...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 15:05:06

give us some sample data, I'd like to see the difference. Which one is right? I have a feeling the ANSI one is .... the *= syntax can lead to ambigiouity.

either way, what is the logic of your SQL statement? what are you trying to return?

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-14 : 15:06:50

I think Brett missed something:


SELECT COUNT(*) 
      FROM Store a
 LEFT JOIN FranchEst b
        ON a.iStoreId=b.iStoreID
 RIGHT JOIN Report_FWeek c
        ON b.dtWeekEnd=c.WeekEndingDate 
INNER JOIN Franchisee d
        ON a.iFranchiseeid=d.iFranchiseeid

quote:
Very interesting... but now I'm getting considerably more rows with the new query. I've had a couple other gurus take a look at it and they seem to think it might not be possible to represent this exact same query using ANSI joins...

I dont agree with that, post your table structure, sample data and expected results, I am sure we could help you with it.

OS

Glockenspiel
Yak Posting Veteran

50 Posts

Posted - 2003-05-14 : 15:13:53

I wish I had more history on it; unfortunately, this is an ancient SQL Server 6.5 routine and the original creators are long gone. We have been tasked with converting this to SQL Server 2000 -- luckily, the old non-ANSI join seems to work OK even though the database is in 2000 mode (i.e., not 6.5 compatibility mode). If possible, I would prefer to use the ANSI joins, though.

When I join the 'a' and the 'd' table I get 1232 rows, which is the same number of rows that I get when running the legacy query. The troubling part seems to be the join between the 'c' table and the 'b' table. The 'c' table only has one row in whereas the 'b' table has about 70,000 rows.

It looks to me like the problem is with having both a left and a right join... <grrr>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-14 : 15:14:03

Good catch, mohdowais!

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 15:14:45

Yup, sure did...

It's not been a good day....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources