InnerJoin + Left Outer Join syntax problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug - 02 Mar 2006 22:47 GMT

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

Reply to this Message

Anthony England - 02 Mar 2006 23:18 GMT

> Hi,
>
[quoted text clipped - 21 lines]
>
> 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.

Reply to this Message

Doug - 03 Mar 2006 00:04 GMT

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

>> Hi,
>>
[quoted text clipped - 36 lines]
> written, including changing LEFT OUTER JOIN to LEFT JOIN as the OUTER bit
> is superfluous.

Reply to this Message

Anthony England - 03 Mar 2006 15:02 GMT

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

Reply to this Message