Left join on three fields giving strange results

[Seth Schrock]

I have two queries that I'm trying to left join together based on three fields. When I do, I'm getting the results of an INNER join (only the two records that match in both queries). If I remove one of the fields from the join (making the join on two fields), then I get the results that I'm looking for - all the records from the left table and the two records that match up.

Here is the query with the three fields left joined that isn't working:

SELECT Base.LoanId_fk
     , Base.TaskId_pk
     , Base.Description
     , Base.TaskOrder
     , Base.ValueLocation
     , LoanValue.FieldName
     , LoanValue.FieldValue
FROM   tqryTasks_Pro_Base AS Base
       LEFT JOIN
       tqryTasks_Pro_LoanValue AS LoanValue 
  ON   (Base.ValueLocation = LoanValue.ValueLocation) 
 AND   (Base.LookupValue = LoanValue.FieldName) 
 AND   (Base.LoanId_fk = LoanValue.LoanId_pk);

Here is the same query, but only joined on two fields that works.

SELECT Base.LoanId_fk
     , Base.TaskId_pk
     , Base.Description
     , Base.TaskOrder
     , Base.ValueLocation
     , LoanValue.FieldName
     , LoanValue.FieldValue
FROM   tqryTasks_Pro_Base AS Base
       LEFT JOIN
       tqryTasks_Pro_LoanValue AS LoanValue 
  ON   (Base.LookupValue = LoanValue.FieldName) 
 AND   (Base.LoanId_fk = LoanValue.LoanId_pk);

Is there some syntax that I'm missing to make it work with three fields in the join? I've played around with the parenthesis, adding a set around the entire ON clause, as I saw on one article I found while Googling the issue, but no change. What am I missing?

[NeoPa]

I can only guess really Seth as I see nothing wrong with your SQL.

However, I've seen situations where the queries being JOINed use calculations and that might give you a problem. [Loan].[ValueLocation] would be the one to check I suspect.

[jimatqsi]

Are either of these two fields ever NULL when you run the queries independently, Base.ValueLocation and LoanValue.ValueLocation?

If so, handling that condition in the JOIN might solve the problem.

Jim

[Seth Schrock]

@Jim
Neither field is ever NULL.

@NeoPa
[Loan].[ValueLocation] is kind of a calculated field. It is defined as "Loan Value" AS ValueLocation in the [Loan] query. Do you think that is what is causing the strange behavior?

Also, both queries are based on ODBC linked tables. I'm not sure if that would change any characteristics.

[isladogs]

"[Loan].[ValueLocation] is kind of a calculated field. It is defined as "Loan Value" AS ValueLocation in the [Loan] query. Do you think that is what is causing the strange behavior?"

No - that is just an ALIAS
It shouldn't matter that these are ODBC linked tables UNLESS the fields use a datatype Access can't handle correctly

[Seth Schrock]

[Base].[ValueLocation] is a varchar data type. The other side is the alias. All the fields in the joins are either int or varchar.

[Seth Schrock]

Well, it turns out that NeoPa was correct. I moved the alias field from the access query to the SQL Server view and now the join works. Crazy. Not sure why that made a difference, but there we go.

[jimatqsi]

In your query, was that

 [Loan Value] as ValueLocation

? I mean [ ] not " " as you described it in the SQL? Just wondering if you left out the brackets and that caused this very strange behavior.

[Seth Schrock]

Originally, the Loan query had the field ValueLocation as just text in double quotes, not a field name in brackets. To fix it, I moved the plain text into the SQL view.