Help with LEFT JOIN Multiple ON clauses

[Mike]

Hi - I can't figure out how to format my LEFT JOIN shown below. Access gives
me the error "syntax error (missing operator)" * doesn't like my multiple ON
clauses. How do I format these multiple ON clauses? Thanks!

SELECT Asset.*, DocClass.ClassID, DocClass.Class, Transaction.*, IIF(NOT
ISNULL(Transaction.AssetID),getLastTransctionDollarAmount([Transaction.AssetID]),0) AS RemainingBalance
FROM (Asset INNER JOIN DocClass ON Asset.ClassID = DocClass.ClassID)
LEFT JOIN [Transaction] ON Asset.AssetID = Transaction.AssetID
AND ON Transaction.boolRecordActive = False

 

[Ken Sheridan]

I think what you are trying to do is:

SELECT Asset.*, DocClass.ClassID, DocClass.Class, Transaction.*, IIF(NOT
ISNULL(Transaction.AssetID),getLastTransctionDollarAmount([Transaction.AssetID]),0) AS RemainingBalance
FROM (Asset INNER JOIN DocClass ON Asset.ClassID = DocClass.ClassID)
LEFT JOIN [Transaction] ON Asset.AssetID = Transaction.AssetID
WHERE Transaction.boolRecordActive = FALSE;

However you can't restrict a query using a LEFT OUTER JOIN on a column in a
table on the right side of the join. If you think about it it’s a logical
inconsistency to do so, as on the one hand you are saying return all rows
from the tables on the left side of the join regardless of whether there is a
matching row on the right side, but on the other hand saying only return rows
where there is a particular value in a column in the table on the right side.
This trying to have your relational cake and eat it, and the result in
effect changes the LEFT OUTER JOIN to an INNER JOIN.

To do what I think you want first create a query on the Transaction table
WHERE boolRecordActive = FALSE, then in the above query LEFT JOIN to this new
query instead of the Transaction table and omit the WHERE clause.

Ken Sheridan
Stafford, England

 

[Sylvain Lafontaine]

You must remove the second ON and probably put a parenthesis around the AND,
after the ON; something like:

SELECT Asset.*, DocClass.ClassID, DocClass.Class, Transaction.*, IIF(NOT
ISNULL(Transaction.AssetID),getLastTransctionDollarAmount([Transaction.AssetID]),0)
AS RemainingBalance
FROM (Asset INNER JOIN DocClass ON Asset.ClassID = DocClass.ClassID)
LEFT JOIN [Transaction] ON (Asset.AssetID = Transaction.AssetID
AND Transaction.boolRecordActive = False)

However, as you didn't provide the schema of your table, I didn't try it.

 

[Mike]

Thanks for the info!

I think what you are trying to do is:

SELECT Asset.*, DocClass.ClassID, DocClass.Class, Transaction.*, IIF(NOT
ISNULL(Transaction.AssetID),getLastTransctionDollarAmount([Transaction.AssetID]),0) AS RemainingBalance
FROM (Asset INNER JOIN DocClass ON Asset.ClassID = DocClass.ClassID)
LEFT JOIN [Transaction] ON Asset.AssetID = Transaction.AssetID
WHERE Transaction.boolRecordActive = FALSE;

However you can't restrict a query using a LEFT OUTER JOIN on a column in a
table on the right side of the join. If you think about it it’s a logical
inconsistency to do so, as on the one hand you are saying return all rows
from the tables on the left side of the join regardless of whether there is a
matching row on the right side, but on the other hand saying only return rows
where there is a particular value in a column in the table on the right side.
This trying to have your relational cake and eat it, and the result in
effect changes the LEFT OUTER JOIN to an INNER JOIN.

To do what I think you want first create a query on the Transaction table
WHERE boolRecordActive = FALSE, then in the above query LEFT JOIN to this new
query instead of the Transaction table and omit the WHERE clause.

Ken Sheridan
Stafford, England

Hi - I can't figure out how to format my LEFT JOIN shown below. Access gives
me the error "syntax error (missing operator)" * doesn't like my multiple ON
clauses. How do I format these multiple ON clauses? Thanks!

SELECT Asset.*, DocClass.ClassID, DocClass.Class, Transaction.*, IIF(NOT
ISNULL(Transaction.AssetID),getLastTransctionDollarAmount([Transaction.AssetID]),0) AS RemainingBalance
FROM (Asset INNER JOIN DocClass ON Asset.ClassID = DocClass.ClassID)
LEFT JOIN [Transaction] ON Asset.AssetID = Transaction.AssetID
AND ON Transaction.boolRecordActive = False