By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Efficient way to compare data in 3 tables and only show the discrepancy

P: 27
Is there a way to triple check that the data is consistent between the three tables and show if there are any discrepancies in any of the three tables? Each table is not completely the same but they all have an EmployeeID, amount, datepaid, and CID column. When I did with two tables I thought I had the SQL as:

Expand|Select|Wrap|Line Numbers
  1. SELECT tbMaster.EmployeeID, tbMaster.Amount, tbMaster.DatePaid, tbMaster.CID
  2. FROM tbMaster LEFT JOIN tbCID ON (tbMaster.CID = tbSUID.CID) AND (tbMaster.Amount = tbCID.Amount) AND (tbMaster.DatePaid = tbCID.DatePaid)
  3. WHERE (((tbMaster.[Advance/paid])="Paid Respondent") AND ((tbCID.CID) Is Null));
It worked but if the tbCID had any discrepancies it did not catch it...

So for the three tables I thought:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbMaster.EmployeeID, tbMaster.Amount, tbMaster.DatePaid, tbMaster.[Advance/paid], tbMaster.CID
  2. FROM tbMaster, tbCID, table3
  3. WHERE tbMaster.CID <> tbCID.CID
  4. OR  table3.CID <> tbMaster.CID
  5. OR  table3.CID <> tbCID.CID
  6. OR tbMaster.Amount <> tbCID.Amount
  7. OR  table3.AMOUNT <> tbMaster.AMOUNT
  8. OR  table3.AMOUNT <> tbCID.AMOUNT
  9. OR tbMaster.Datepaid <> tbCID.Datepaid
  10. OR  table3.DATEPAID <> tbMaster.DATEPAID
  11. OR  table3.DATEPAID <> tbCID.DATEPAID
  12.  
But there is only 30 entries yet I get 5x or 6x copies and get over 3000 rows/entries in the query...
Mar 8 '19 #1
Share this Question
Share on Google+
7 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
BA,

You would NEVER want to create tables like that. You should never have three tables that have duplicate entries like that. All you need is the one table that has that information and have the EmployeeID be the foreign key to that table from any other tables.

As you have demonstrated, this is the reason you donít duplicate values, because now you have to determine if there are any discrepancies.
Mar 9 '19 #2

P: 27
Thank you for your response twinnyfo. I agree I would rather just handle one table. Unfortunately I do not control the tables being added and was informed they wanted to triple check the amount, date, and ID so have three tables where they will ask the employees to fill 3x...
I guess I can append query all three tables into one table then do an unmatch query?
Mar 11 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
1. You are working on the DB, correct? How come you do not control the tables?

2. If the data is in the tables once, there is no need to triple check any data.

3. If I was an employee I would be asking, "Why do I have to enter this three times? It's the same data!"

4. If you are running an append query, with the same data across three tables, again, there is no need to either triple check or run an "unmatch" query--whatever that may be, becauase the data is (should be) the same.
Mar 11 '19 #4

P: 27
Hi twinnyfo
I am just someone who makes the frame and am told what to try to make it do. The tables are one for before they pay, when they pay, and a table of receipts to check if what they say they will pay/have paid is what the receipt says? if that makes sense. Yes we are hoping that the data should be the same but I guess there were instances where someone fat fingered an extra digit and got paid extra.
Mar 11 '19 #5

P: 27
I think I got it!
Expand|Select|Wrap|Line Numbers
  1. SELECT tbMaster.EmployeeID, tbMaster.Amount, tbMaster.DatePaid, tbMaster.[Advance/paid], tbMaster.CID
  2. FROM (tbMaster LEFT JOIN tbCID ON (tbMaster.DatePaid = tbCID.DatePaid) AND (tbMaster.Amount = tbCID.Amount) AND (tbMaster.CID = tbCID.CID)) LEFT JOIN table3 ON (tbMaster.CID = table3.CID) AND (tbMaster.DatePaid = table3.DatePaid) AND (tbMaster.Amount = table3.Amount)
  3. WHERE (((tbMaster.[Advance/paid])="Paid Respondent") AND ((tbCID.CID) Is Null)) OR (((tbMaster.[Advance/paid])="Paid Respondent") AND ((table3.CID) Is Null));
  4.  
Thank you!
Mar 11 '19 #6

twinnyfo
Expert Mod 2.5K+
P: 3,284
Have fun with this. As your records and requirements expand, this structure will become your primary headache. I don't tell my bosses how to set employee policy. They don't try to tell me how to design the structure of my tables.
Mar 11 '19 #7

Rabbit
Expert Mod 10K+
P: 12,366
Be careful here, that query might not do what you think it does. That query only tells you if there are paid respondent records in the master table that does not exist in table CID or table 3. It does not check the reverse of that situation and it does not check table CID against table 3.

With 3 tables, there are 6 scenarios for missing records, your query only checks 2 of those 6.
Mar 11 '19 #8

Post your reply

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