Syntac error (missing operator)

[Mark]

Hi - I can get this to work in SQL Server - but when also trying to make
the application compatible with MS Access I get an error:

Select tblfaqnetgroups .group_name from tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

The error in Access is:

Syntax error (missing operator) in query expression
'tblfaqnetroles .group_id = tblfaqnetgroups .group_id
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id'

Any help would be much appreciated,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Syntac error (missing operator)


"Mark" <anonymous@devd ex.com> wrote in message
news:3ffbba08$0 $195$75868355@n ews.frii.net...[color=blue]
> Hi - I can get this to work in SQL Server - but when also trying to make
> the application compatible with MS Access I get an error:
>
> Select tblfaqnetgroups .group_name from tblfaqnetroles
> Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
> tblfaqnetgroups .group_id
> Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
> AND tblaccess.user_ id = 1
>
> The error in Access is:
>
> Syntax error (missing operator) in query expression
> 'tblfaqnetroles .group_id = tblfaqnetgroups .group_id
> Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id'
>
> Any help would be much appreciated,
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

This is more of an Access question, but it seems that Access requires
parentheses:

Select tblfaqnetgroups .group_name from ( tblfaqnetroles
Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
tblfaqnetgroups .group_id )
Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
AND tblaccess.user_ id = 1

This syntax appears to work in MSSQL also, but the parentheses look
confusing (at least to me), as they suggest a derived table where there
isn't one.

Simon

[Erland Sommarskog]

Re: Syntac error (missing operator)

Simon Hayes (sql@hayes.ch) writes:[color=blue]
> This is more of an Access question, but it seems that Access requires
> parentheses:
>
> Select tblfaqnetgroups .group_name from ( tblfaqnetroles
> Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
> tblfaqnetgroups .group_id )
> Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
> AND tblaccess.user_ id = 1
>
> This syntax appears to work in MSSQL also, but the parentheses look
> confusing (at least to me), as they suggest a derived table where there
> isn't one.[/color]

While I agree that the parentheses here are only white noice, there
are cases where you need them:


SELECT ...
FROM a
LEFT JOIN (b JOIN c ON b.col = c.col) ON a.col = b.col

Here, you (logically) first join b and c, and then you to an outer
join between a and this result.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Hayes]

Re: Syntac error (missing operator)

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns946A74 44EFACYazorman@ 127.0.0.1>...[color=blue]
> Simon Hayes (sql@hayes.ch) writes:[color=green]
> > This is more of an Access question, but it seems that Access requires
> > parentheses:
> >
> > Select tblfaqnetgroups .group_name from ( tblfaqnetroles
> > Inner Join tblfaqnetgroups ON tblfaqnetroles. group_id =
> > tblfaqnetgroups .group_id )
> > Inner Join tblaccess ON tblfaqnetroles. user_id = tblaccess.user_ id
> > AND tblaccess.user_ id = 1
> >
> > This syntax appears to work in MSSQL also, but the parentheses look
> > confusing (at least to me), as they suggest a derived table where there
> > isn't one.[/color]
>
> While I agree that the parentheses here are only white noice, there
> are cases where you need them:
>
>
> SELECT ...
> FROM a
> LEFT JOIN (b JOIN c ON b.col = c.col) ON a.col = b.col
>
> Here, you (logically) first join b and c, and then you to an outer
> join between a and this result.[/color]

Thanks for the clarification - I didn't mention this since the
original post is an inner join, but that may have misled the original
poster (hopefully not).