Solved

JOINING on TWO FIELDS in Microsoft Access 97

Posted on 2008-06-18
10
209 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
  • 6
  • 3
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 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
 
LVL 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 142

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now