471,893 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

Left join on three fields giving strange results

Seth Schrock
2,965 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?
Sep 27 '21 #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 5944
NeoPa
32,470 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.
Sep 28 '21 #2
jimatqsi
1,263 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
Sep 28 '21 #3
Seth Schrock
2,965 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.
Sep 28 '21 #4
isladogs
405 Expert Mod 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
Sep 28 '21 #5
Seth Schrock
2,965 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.
Sep 28 '21 #6
Seth Schrock
2,965 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.
Sep 28 '21 #7
jimatqsi
1,263 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.
Sep 28 '21 #8
Seth Schrock
2,965 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.
Sep 29 '21 #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
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.