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.ValueLocat ion and LoanValue.Value Location?

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.