469,301 Members | 2,272 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

Left join on three fields giving strange results

Seth Schrock
2,962 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Base.LoanId_fk
  2.      , Base.TaskId_pk
  3.      , Base.Description
  4.      , Base.TaskOrder
  5.      , Base.ValueLocation
  6.      , LoanValue.FieldName
  7.      , LoanValue.FieldValue
  8. FROM   tqryTasks_Pro_Base AS Base
  9.        LEFT JOIN
  10.        tqryTasks_Pro_LoanValue AS LoanValue 
  11.   ON   (Base.ValueLocation = LoanValue.ValueLocation) 
  12.  AND   (Base.LookupValue = LoanValue.FieldName) 
  13.  AND   (Base.LoanId_fk = LoanValue.LoanId_pk);
Here is the same query, but only joined on two fields that works.
Expand|Select|Wrap|Line Numbers
  1. SELECT Base.LoanId_fk
  2.      , Base.TaskId_pk
  3.      , Base.Description
  4.      , Base.TaskOrder
  5.      , Base.ValueLocation
  6.      , LoanValue.FieldName
  7.      , LoanValue.FieldValue
  8. FROM   tqryTasks_Pro_Base AS Base
  9.        LEFT JOIN
  10.        tqryTasks_Pro_LoanValue AS LoanValue 
  11.   ON   (Base.LookupValue = LoanValue.FieldName) 
  12.  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?
3 Weeks Ago #1

✓ answered by 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.

8 5081
NeoPa
32,173 Expert Mod 16PB
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.
3 Weeks Ago #2
jimatqsi
1,260 Expert 1GB
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
3 Weeks Ago #3
Seth Schrock
2,962 Expert 2GB
@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.
3 Weeks Ago #4
isladogs
291 Expert 256MB
"[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
3 Weeks Ago #5
Seth Schrock
2,962 Expert 2GB
[Base].[ValueLocation] is a varchar data type. The other side is the alias. All the fields in the joins are either int or varchar.
3 Weeks Ago #6
Seth Schrock
2,962 Expert 2GB
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.
3 Weeks Ago #7
jimatqsi
1,260 Expert 1GB
In your query, was that
Expand|Select|Wrap|Line Numbers
  1.  [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.
3 Weeks Ago #8
Seth Schrock
2,962 Expert 2GB
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.
3 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Hunter Hillegas | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.