ANSI-92 inner join vs. where clause syntax

Having problems rewriting my join condition using the "inner join" syntax.

My query, working with an intersection table:

SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur
WHERE
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID

This works fine, but i want to write it using 'inner join' style, so I tried:

SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u
INNER JOIN Accounts_UserRoles ur
ON
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID

which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.)

Any ideas as to how I'm screwing this up would be appreciated.

Thanks,
Gordon ZI'd use:SELECT Description, EmailAddress
FROM Accounts_UserRoles ur
INNER JOIN Accounts_Roles r
ON (r.RoleID = ur.RoleID)
INNER JOIN Accounts_Users u
ON (u.UserID = ur.UserID)-PatP|||Someone here (at Rackspace) has already come up with a cleche based on talking to me: Take the JOIN out of your WHERE clause (and then adding something like "...and take your head out of your @.$$"), which is actually based on what Rudy (come out and play here!!!) would enumerate for you very accurately, but I just say that WHERE is processed after JOIN. So I'll let you come up to your own conclusion ;)|||i really admire people who recognize that there is a difference between JOIN syntax and the older table list method, and are trying to make the change and learn the better way

Ansi-92 in sql server 2005

Is there any flag to enable ansi-92 joins syntax for sql server 2005?
-NunoNuno wrote:
> Is there any flag to enable ansi-92 joins syntax for sql server 2005?
> -Nuno
SQL Server 2005 supports the ANSI-92 syntax [ INNER | { { LEFT | RIGHT
| FULL } [ OUTER] } ] by default. You don't need to enable it.
Maybe you mean the old-style *= syntax (which is not an ANSI standard).
To use that in 2005 you have to set the compatibility level to 80 or
earlier:
EXEC sp_dbcmptlevel 'database_name',80 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||As I understand it (probably wrong). The SQL 92 standard was approved
by both ANSI and ISO, so it's a fair point to refer to it as an ANSI
standard.|||Sorry - i see now...
You're arguing that the *= isn't in the ansi standard, rather than that
the standard 92 wasn't an ANSI standard.
My bad.|||What standard was the old style? Or was that microsoft specific?
I know in Oracle I used to use column1(+) = column2, which I have seen
referred to as SQL 1989 standard.
Just curious...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147382385.927779.244890@.v46g2000cwv.googlegroups.com...
> Nuno wrote:
> SQL Server 2005 supports the ANSI-92 syntax [ INNER | { { LEFT | RIGHT
> | FULL } [ OUTER] } ] by default. You don't need to enable it.
> Maybe you mean the old-style *= syntax (which is not an ANSI standard).
> To use that in 2005 you have to set the compatibility level to 80 or
> earlier:
> EXEC sp_dbcmptlevel 'database_name',80 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks David. Thats exactly what I meant.
"Jim Underwood" wrote:

> What standard was the old style? Or was that microsoft specific?
> I know in Oracle I used to use column1(+) = column2, which I have seen
> referred to as SQL 1989 standard.
> Just curious...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1147382385.927779.244890@.v46g2000cwv.googlegroups.com...
>
>|||Would setting the compatibility mode to SQL 2000(80) have any affect on the
new 2005 features such as the new ability to use .net assemblies in
conjunction with queries?
Also(one last question), what are the benifits(if any) of having the
compatibility mode on (90) vs (80)?
Thanks a million for the help,
Nuno
"David Portas" wrote:

> Nuno wrote:
> SQL Server 2005 supports the ANSI-92 syntax [ INNER | { { LEFT | RIGHT
> | FULL } [ OUTER] } ] by default. You don't need to enable it.
> Maybe you mean the old-style *= syntax (which is not an ANSI standard).
> To use that in 2005 you have to set the compatibility level to 80 or
> earlier:
> EXEC sp_dbcmptlevel 'database_name',80 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Nuno wrote:
> Would setting the compatibility mode to SQL 2000(80) have any affect on th
e
> new 2005 features such as the new ability to use .net assemblies in
> conjunction with queries?
> Also(one last question), what are the benifits(if any) of having the
> compatibility mode on (90) vs (80)?
> Thanks a million for the help,
> Nuno
See sp_dbcmptlevel for an explanation of the backwards-compatibility
features. Although certain new features will work with compatibility
set to 80 this isn't entirely documented so you may be on your own. In
general if you want to use new features it would be best to select 90
compatibility. It's certainly a good idea to re-write your old joins if
you are able to.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Jim Underwood wrote:
> What standard was the old style? Or was that microsoft specific?
> I know in Oracle I used to use column1(+) = column2, which I have seen
> referred to as SQL 1989 standard.
> Just curious...
>
*= came from Sybase. The + syntax was used by Oracle (and DB2?). I'm
pretty sure that neither were in the ANSI 89 standard. I noticed that
at least some Oracle users prior to version 9 were under the illusion
that the product used Standard SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

ANSI Nulls SQL Server Setting

Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI null
s
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,

ANSI Nulls SQL Server Setting

Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI nulls
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,
ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,

ANSI Nulls SQL Server Setting

Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI nulls
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,