SEO

#1 (**permalink**) 02-28-2008, 07:25 PM

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!

#2 (**permalink**) 02-28-2008, 07:26 PM

"Mark" <anonymous@devdex.com> wrote in message
news:3ffbba08$0$195$75868355@news.frii.net...
> 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!

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

#3 (**permalink**) 02-28-2008, 07:26 PM

Simon Hayes (sql@hayes.ch) writes:
> 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.

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/techinf...2000/books.asp

#4 (**permalink**) 02-28-2008, 07:27 PM

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns946A7444EFACYazorman@127.0.0.1>...
> Simon Hayes (sql@hayes.ch) writes:
> > 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.
>
> 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.

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).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Syntac error (missing operator)