473,884 Members | 2,358 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Left join on three fields giving strange results

Seth Schrock
2,965 Recognized Expert Specialist
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
8 6164
NeoPa
32,584 Recognized Expert Moderator MVP
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,278 Recognized Expert Top Contributor
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
Sep 28 '21 #3
Seth Schrock
2,965 Recognized Expert Specialist
@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
462 Recognized Expert Moderator Contributor
"[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 Recognized Expert Specialist
[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 Recognized Expert Specialist
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,278 Recognized Expert Top Contributor
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 Recognized Expert Specialist
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

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

Similar topics

0
2370
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
1
2597
by: Hunter Hillegas | last post by:
I have just added a third table to a query and I am no longer getting the results I am expecting. Three Tables: CUSTINVOICEJOUR (Header Table) CUSTINVOICETRANS (Line Item Table) MARKUPTRANS (Additional Header Info) CUSTINVOICEJOUR has a one to many relationship to CUSTINVOICETRANS.
7
2106
by: Julian32 | last post by:
Hello All, Before this I'd never beefore needed to use the "LEFT JOIN" command, and unfortunately it's giving me some problems. I have three tables setup as follows (note they are test tables for a prototype of a larger project): user_table: | UserID | Username | Password| | 1 | julian | abcdef | | 2 | test | test |
2
4060
by: David F | last post by:
Hello, I have what seems like a simple left join query, but Access returns results like it's an inner join. I've tried numerous combinations without success. I have a table (ProjectList) with one row per project. Each project has a status condition of Green, Yellow or Red. The other table (DisplayOrder) has three rows, one for each status condition color.
2
1432
by: imnewtoaccess | last post by:
Hello everybody, I have two tables : ac_online_scanned_tkts_1 and TK_IMPORT_TICKET1. Both have external barcodes of tickets, but not all of them in ac_online_scanned_tkts_1 table exist in TK_IMPORT_TICKET1 table. I want a list of all those external barcodes that exist in ac_online_scanned_tkts_1 table but do not exist in TK_IMPORT_TICKET1 table. I am using the follwoing LEFT JOIN query to get these results, but I am not getting the desired...
2
1578
by: SP | last post by:
I have two tables, A & B. Table B has adjustment factors of three types, "COMM" being one of them. I want a query that generates all rows of table A, and those where certain fields match, I want the value of A.PD01 to be multiplied by the the "COMM" factor. I have the following SQL, but it only generates the records where the fields match, not all of them. SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS, !*! AS PD01
2
1932
dlite922
by: dlite922 | last post by:
I've heard this may not be possible, but never hurts to ask. I've got three tables, Case, Violator, Alias Case is a court police case Violator is the person Alias is different names (aliases) that person has. Here's my Query:
7
2182
by: atlanteavila | last post by:
Man have I hit a pitfall on this one--I need help figuring out how to join three tables to display results in this format: Obi One downloaded adobe.pdf downloaded apples.pdf downloaded mymusic.mp3 Luke Skywalker - 555-5455 downloaded sample.pdf downloaded apples.pdf
2
1734
by: A Williams | last post by:
I have two queries, one using a right join and one using a left join that are producing different results, but they should be the same. The RIGHT join is acting like an inner join. Please see the queries below . . . In the first one I have the workorder (w) table on the RIGHT side of the ON statement and a RIGHT join. In the second one, I have the the workorder (w) table on the LEFT side of the ON statement and a LEFT join, so these two...
7
1468
Seth Schrock
by: Seth Schrock | last post by:
I have a query the among other things, converts a date/time field (TransactionDateTime) to just a date value using the DateValue() function (TransactionDate). I have another query that is based on the customers table with a left join to the above query (the query is the right "table"). So basically, I get every customer with the transactions that link up. However I'm getting a weird result. Customer ID 4 has no transactions, so all my fields...
0
9954
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11168
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10869
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7137
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5808
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6009
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4231
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.