471,853 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

InnerJoin + Left Outer Join syntax problem

Hi,

I'm more familiar with MSSQL than Access syntax and have run into a problem
with correctly putting ( )'s around the joins in a 3 table query. I want to
INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with
prospects. (I want to receive all the results of the inner join and any
pertinent info from table 3 that is available.)

The way it was written in MSSQL was basically..

SELECT a.lenderID, a.dateentered, a.accountID, a.prospectID
FROM lenders AS L INNER JOIN accounts AS a ON L.lenderID = a.lenderID
LEFT OUTER JOIN prospects as p ON p.prospectID = a.prospectID
WHERE a.lenderID=123 AND a.dateentered<#3/2/2006# AND
a.dateentered>=#2/1/2006#;

What I dont understand is how/where to put the parens as required by Access.
Unfortunately, I have no books that discuss this for Access, only ones for
SQL and MySQL.

Any help would be appreciated.

Thanks,

Doug
Mar 2 '06 #1
3 17660

"Doug" <pe*******@comcast.net> wrote in message
news:df******************************@comcast.com. ..
Hi,

I'm more familiar with MSSQL than Access syntax and have run into a
problem with correctly putting ( )'s around the joins in a 3 table query.
I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that result
with prospects. (I want to receive all the results of the inner join and
any pertinent info from table 3 that is available.)

The way it was written in MSSQL was basically..

SELECT a.lenderID, a.dateentered, a.accountID, a.prospectID
FROM lenders AS L INNER JOIN accounts AS a ON L.lenderID = a.lenderID
LEFT OUTER JOIN prospects as p ON p.prospectID = a.prospectID
WHERE a.lenderID=123 AND a.dateentered<#3/2/2006# AND
a.dateentered>=#2/1/2006#;

What I dont understand is how/where to put the parens as required by
Access. Unfortunately, I have no books that discuss this for Access, only
ones for SQL and MySQL.

Any help would be appreciated.

Thanks,

Doug

Just put parantheses around the first bit:

SELECT a.lenderID, a.dateentered, a.accountID, a.prospectID
FROM (lenders AS L INNER JOIN accounts AS a
ON L.lenderID=a.lenderID) LEFT OUTER JOIN
prospects as p ON p.prospectID=a.prospectID
WHERE a.lenderID=123 AND
a.dateentered<#3/2/2006# AND
a.dateentered>=#2/1/2006#

If you cut and paste this into the SQL view of the query, then save the
query, you should find that Access will slightly adjust what you have
written, including changing LEFT OUTER JOIN to LEFT JOIN as the OUTER bit is
superfluous.
Mar 2 '06 #2
Anthony,

Thank you, thank you, thank you! This works great. Now if I only knew WHY
I have to use the parentheses or could find a syntax page....

Doug
"Anthony England" <ae******@oops.co.uk> wrote in message
news:du**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"Doug" <pe*******@comcast.net> wrote in message
news:df******************************@comcast.com. ..
Hi,

I'm more familiar with MSSQL than Access syntax and have run into a
problem with correctly putting ( )'s around the joins in a 3 table query.
I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that
result with prospects. (I want to receive all the results of the inner
join and any pertinent info from table 3 that is available.)

The way it was written in MSSQL was basically..

SELECT a.lenderID, a.dateentered, a.accountID, a.prospectID
FROM lenders AS L INNER JOIN accounts AS a ON L.lenderID = a.lenderID
LEFT OUTER JOIN prospects as p ON p.prospectID = a.prospectID
WHERE a.lenderID=123 AND a.dateentered<#3/2/2006# AND
a.dateentered>=#2/1/2006#;

What I dont understand is how/where to put the parens as required by
Access. Unfortunately, I have no books that discuss this for Access, only
ones for SQL and MySQL.

Any help would be appreciated.

Thanks,

Doug

Just put parantheses around the first bit:

SELECT a.lenderID, a.dateentered, a.accountID, a.prospectID
FROM (lenders AS L INNER JOIN accounts AS a
ON L.lenderID=a.lenderID) LEFT OUTER JOIN
prospects as p ON p.prospectID=a.prospectID
WHERE a.lenderID=123 AND
a.dateentered<#3/2/2006# AND
a.dateentered>=#2/1/2006#

If you cut and paste this into the SQL view of the query, then save the
query, you should find that Access will slightly adjust what you have
written, including changing LEFT OUTER JOIN to LEFT JOIN as the OUTER bit
is superfluous.

Mar 3 '06 #3
"Doug" <pe*******@comcast.net> wrote in message
news:cb******************************@comcast.com. ..
Anthony,

Thank you, thank you, thank you! This works great. Now if I only knew
WHY I have to use the parentheses or could find a syntax page....

Doug

I don't have a page reference handy - there's the help files but they are
not often much help! I know that Access will accept certain things, but
then re-write them if you save the query, eg

SELECT ComName FROM
(SELECT ComCode, ComName FROM tblCompany
WHERE ComCode LIKE "A*") AS AliasTable

would be re-written as

SELECT AliasTable.ComName FROM
[SELECT ComCode, ComName FROM tblCompany
WHERE ComCode LIKE "A*"]. AS AliasTable;

Note the square brackets and the dot.
Anyway, surely someone from this group can recomend a website for SQL
syntax...
Mar 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ian Boyd | last post: by
5 posts views Thread by Dev | last post: by
1 post views Thread by Eitan M | last post: by
3 posts views Thread by rrstudio2 | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.