[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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

0
MeridianManagement
Asked:
MeridianManagement
  • 6
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]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 HornMicrosoft 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now