1

I have two MySQL tables within the same Database that I need to compare. One is masterTxn, which essentially is a master list of transactions that I need to compare with another table, month_tbl.

My goal is to compare masterTxn with month_tbl and find the transactions that are within masterTxn but are not in month_tbl. I've tried the following, but it returns 0 rows, when I know there are definitely at least 300-400 rows that should match this query:

SELECT * FROM masterTxn WHERE masterTxn.TxnID NOT IN (SELECT month_tbl.TXNID FROM month_tbl)

As you can see, I'm trying to compare using the TXNID column in each of the tables. However, here's the catch: There are multiple rows with the same TXNID value. In other words, there is no primary key or identifier for either of these tables—although I wish I could change that, that is how the data is written from it's source.

I've tried several things from these other posts:
MySQL "NOT IN" query
Get a result by comparing two tables with an identical column

But these ideas haven't worked... Perhaps I'm implementing them wrong, but it seems like each of these other posts has a column where there is no other row that has the same value.

Any ideas?

Thanks in advance!!

Community
  • 1
  • 1
Ben Holland
  • 321
  • 1
  • 8

1 Answers1

1

Try this:

SELECT a.*
FROM masterTxn a
LEFT JOIN month_tbl b ON b.TXNID = a.TXNID
WHERE b.TXNID IS NULL
  • Thanks for your response @PhillipXT! I tried that and it looks like it worked with the right amount of results! However, it took almost 12 minutes. Do you have any suggestions for speeding it up? The month_tbl has 99k rows and the masterTxn has 34k. Is that the reason? Or do I need to beef up the MySQL server? – Ben Holland Nov 08 '14 at 01:25
  • To speed up the query, add an index on `month_tbl(TXNID)`. – Gordon Linoff Nov 08 '14 at 02:48