Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

JOINING on TWO FIELDS in Microsoft Access 97

Posted on 2008-06-18
10
Medium Priority
?
259 Views
Last Modified: 2010-04-21
I've designed a query in SQL view. I have two tables, and unfortunately, the keys that I am joining on are in two separate fields. An account and a user. I'm trying to make sure that the account/user pair exists in the other table. Why not use just the account? Because accounts can span unlimited users, so I have to specifically look for the account/user pair for the join. My code is below, and it only shows user and account from the first table. and not the account from the second table (diicust.Field40 = account)
SELECT inputfile.user, inputfile.account, diicust.Field40
FROM inputfile LEFT JOIN diicust ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user;

Open in new window

0
Comment
Question by:MeridianManagement
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 21816089
>I'm trying to make sure that the account/user pair exists in the other table.
if you want to see which one does NOT exist on the other table:

SELECT inputfile.user, inputfile.account, diicust.Field40
FROM inputfile LEFT JOIN diicust ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user
WHERE diicust.Field14 IS NULL;

if you want only those that are in both tables, use INNER JOIN

if you want only those that are in diicust but NOT in inputfile:

SELECT inputfile.user, inputfile.account, diicust.Field40
FROM diicust LEFT JOIN inputfile ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user
WHERE inputfile.account IS NULL;
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 21816117
SELECT inputfile.user, inputfile.account, diicust.Field40
FROM inputfile
INNER JOIN diicust ON diicust.Field40 = inputfile.account AND diicust.Field14 = inputfile.user

Your SQL looks correct, other than I changed the LEFT join to an INNER join, which requires that both tables meet the join criteria.
A LEFT join returns all the records in inputfile, and only the related diicust values if the join criteria is met.

Are account and user the same data type in both tables?
 
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816126
why is my query not working though?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816164
please clarify "not working".
the SQL statement will only return what you ask it to do, which might differ from what you expect it to do.
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816219
My inputfile table has 14156 records
// this pulls up all the records in input file and according to angelll it's only supposed to pull the ones in the input file that aren't found in diicust. It's returned 14156 records.
 
SELECT inputfile.user, inputfile.account, diicust.Field40, diicust.Field14
FROM inputfile LEFT JOIN diicust ON (diicust.Field14 = inputfile.user) AND (diicust.Field40 = inputfile.account)
WHERE diicust.Field14 IS NULL;
 
// this I know doesn't make sense since I need the pair, but I was just proving that my table data matches. It returned 76,899 records.
 
SELECT inputfile.user, inputfile.account, diicust.Field40, diicust.Field14
FROM inputfile, diicust
WHERE diicust.Field14 = inputfile.user;

Open in new window

0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816238
The outer left join is returning all records, but my second query proves that my data matches.

Would an index on these fields be screwing this up?
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816268
anymore test queries you would like me to run so you can investigate my table structures?
0
 
LVL 2

Author Comment

by:MeridianManagement
ID: 21816279
jimhorn, account and user are number (long integers) in both tables.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816347
>but my second query proves that my data matches.
yes, but only on the user, but not on the account+user pair!

to check, find 1 account+user (manually) of which you know they match (eventually fill in such rows by hand).

then, run the query with limiting to that account+userid in either table, with a inner join.
if no records are returned, there is some issue with the data types.
0
 
LVL 2

Author Closing Comment

by:MeridianManagement
ID: 31468529
Angel, Thanks you were right. A weird zero was appearing on one of the tables, but it was an extra trailing zero! How weird! I exported into excel, used the left function and imported back in. I'm sure you could've told me an easier way but I didn't have much time to go back and forth. Anyway, thanks! You're the best.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question