InnerJoin + Left Outer Join syntax problem

Doug

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

Anthony England

"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

Doug

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

Anthony England

"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