JOINING on TWO FIELDS in Microsoft Access 97

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

LVL 2
MeridianManagementAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
MeridianManagementAuthor Commented:
why is my query not working though?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MeridianManagementAuthor Commented:
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
 
MeridianManagementAuthor Commented:
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
 
MeridianManagementAuthor Commented:
anymore test queries you would like me to run so you can investigate my table structures?
0
 
MeridianManagementAuthor Commented:
jimhorn, account and user are number (long integers) in both tables.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
MeridianManagementAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.