Valid MS Access SQL Syntax (Double JOINtedness)

[Don]

Is the following SQL valid in Access?

SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
P.Type
FROM Apartments AS A
INNER JOIN Residents AS R
ON R.AptNum = A.AptNum
LEFT JOIN Phones AS P
ON P.ResID = R.ResID
AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved';



When I try to run the query, I get the following error:

Syntax error (missing operator) in query expression 'R.AptNum= A.AptNum LEFT
JOIN Phones AS P ON P.ResID = R.ResID'.

Any insight will be greatly appreciated!

Thanks!

Don

 

[Bob Barrows [MVP]]

Is the following SQL valid in Access?

SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
P.Type
FROM Apartments AS A
INNER JOIN Residents AS R
ON R.AptNum = A.AptNum
LEFT JOIN Phones AS P
ON P.ResID = R.ResID
AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved';



When I try to run the query, I get the following error:

Syntax error (missing operator) in query expression 'R.AptNum=
A.AptNum LEFT JOIN Phones AS P ON P.ResID = R.ResID'.

Any insight will be greatly appreciated!

Thanks!

Don

Nothing to do with SQL Server. There is a group called m.p.access.queries.

JetSQL is fussy about nesting joins with parentheses when more than one join
is involved. The best way to build your query is to use the Access Query
Builder in Design View to create your joins, then switch to SQL View to
remove extraneous parentheses (except from the FROM clause) . It's been a
while, but I think this will pass muster in Access:

SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
P.Type
FROM Apartments AS A
INNER JOIN (Residents AS R
LEFT JOIN Phones AS P
ON P.ResID = R.ResID
AND P.Type = 'Work')
ON R.AptNum = A.AptNum
WHERE A.Building = 3
AND R.Status = 'Moved';

Bob Barrows

 

[Don]

Bob,

Thanks for the comments! Your suggestion worked and I am now expanding my
knowledge on the topic. As our ultimate goal is to host this on a SQL
Server, I have had a non-Access mind set (hence my post to
microsoft.public.sqlserver.programming). So much so I did not even think
about using the Query Builder!

Will the Access syntax work on SQL Server? It would be annoying to have to
come back and reconfigure the SQL strings. But I suppose sometimes such is
life!

Thanks!

Don

 

[Bob Barrows [MVP]]

Bob,

Thanks for the comments! Your suggestion worked and I am now
expanding my knowledge on the topic. As our ultimate goal is to host
this on a SQL Server, I have had a non-Access mind set (hence my post
to microsoft.public.sqlserver.programming). So much so I did not
even think about using the Query Builder!

Will the Access syntax work on SQL Server? It would be annoying to
have to come back and reconfigure the SQL strings. But I suppose
sometimes such is life!

The nested joins should work, but you should consider that a one-to-one port
from Access to SQL Server will cause you to fail to take advantage of many
performance enhancements available in SQL Server.

Bob Barrows.

 

[Don]

The nested joins should work, but you should consider that a one-to-one port
from Access to SQL Server will cause you to fail to take advantage of many
performance enhancements available in SQL Server.

Bob Barrows.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob,

Further down the road, we will be able to tune the application. It is a
matter of going from a concept exploration and demo phase (under Access) to
a "working" system under SQL Server.

Thanks again for the help!

Don

 

[Daniel Wilson]

Bob,

Thanks for the comments! Your suggestion worked and I am now expanding my
knowledge on the topic. As our ultimate goal is to host this on a SQL
Server, I have had a non-Access mind set (hence my post to
microsoft.public.sqlserver.programming). So much so I did not even think
about using the Query Builder!

Will the Access syntax work on SQL Server? It would be annoying to have to
come back and reconfigure the SQL strings. But I suppose sometimes such is
life!

Thanks!

Don

The other thing to remember on Access is that multiple OUTER JOINs are not
permitted in a single query. When those are necessary, you must build
stored sub-queries and join them again in your master query.


--
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

 

[Bob Barrows [MVP]]

The other thing to remember on Access is that multiple OUTER JOINs
are not permitted in a single query. When those are necessary, you
must build stored sub-queries and join them again in your master
query.

No, they are allowed. They just have to be in the "same direction" (it's
hard to explain). Again, it's been awhile, so I can't be sure of these
examples, but:

Allowed:
t1 left join t2 on t1.id=t2.id
left join t3 on t2.id=t3.id

Allowed:
t1 left join t2 on t1.id=t2.id
left join t3 on t1.id=t3.id

Allowed (I think):
t1 left join t2 on t1.id=t2.id
right join t3 on t1.id=t3.id

Not allowed:
t1 left join t2 on t1.id=t2.id
right join t3 on t2.id=t3.id

Bob Barrows

 

[Daniel Wilson]

No, they are allowed. They just have to be in the "same direction" (it's
hard to explain). Again, it's been awhile, so I can't be sure of these
examples, but:

Allowed:
t1 left join t2 on t1.id=t2.id
left join t3 on t2.id=t3.id

Allowed:
t1 left join t2 on t1.id=t2.id
left join t3 on t1.id=t3.id

Allowed (I think):
t1 left join t2 on t1.id=t2.id
right join t3 on t1.id=t3.id

Not allowed:
t1 left join t2 on t1.id=t2.id
right join t3 on t2.id=t3.id

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Interesting. I'll have to experiment with this.

I know I have had problems with this before ... and I personally use only
LEFT and INNER joins, not RIGHT. It keeps things simpler in my head.

--
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company