0

I have 2 databases, one for the operations and one for analytics.

The analytics database is not 1:1 the same as the operations database but still very similar.

Now I loaded archive files (csv) into the analytics database (it was empty before).

I have my old data in the analytics and my current data in the operations database.

I want to write a query to find out if some of the newly loaded records were already in the database.

Is there a way to check this out?

EDIT:

First of all I'm sorry. Made a mistake after looking at my database. I don't need to compare two different databases, while copying the operations database via phpmyadmin I forgot to load the current data into the analytics database. So I just need to compare 2 tables in one database, whether some of the old records were already in the database.

My first idea is/was: 

SELECT * 
FROM orderlinesold t1 
LEFT JOIN orderlines t2 on t1.orderid = t2.orderid;

but after running that query I get the hole rows as a result which is impossible --> wrong query.

How can I check up if some of the old data are still in the new one?

PS: Tables are in the comments as a link.

  1. EDIT:

Ok I solved it. Was just a little comparing query:

SELECT * 
FROM ordersold t1  
INNER JOIN orders t2 ON t1.orderid = t2.orderid 
LIMIT 100000000;

or: 

SELECT * 
FROM orderlinesold t1 
LEFT JOIN orderlines t2 ON t1.orderid = t2.orderid 
WHERE t2.orderid IS NOT NULL 
LIMIT 100000000;

Both discard the correct result.

Still thanks. This post can be closed.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JOP
  • 25
  • 1
  • 4
  • 9
  • Please provide the table formats for the two tables. Better yet, provide SQL that gives an example of what you have tried. – Gordon Linoff Mar 27 '13 at 19:50
  • Looks like this thread may help you http://stackoverflow.com/questions/225772/compare-two-mysql-databases?rq=1 . Otherwise you could write queries for each table to analyze that, maybe using an `IN` or `NOT IN` clause. – Scotch Mar 27 '13 at 19:53
  • here my tables: http://imgur.com/XpgwI35 – JOP Mar 27 '13 at 20:06

1 Answers1

0

there are tools to help you compare two databases but the basic technique that you can use, assuming you can rely on a primary key to identify missing records, is:

select    oo.id
from      operations.orders oo
left join analytics.orders ao 
on        oo.id = ao.id
where     ao.id is null
koriander
  • 3,110
  • 2
  • 15
  • 23
  • thanks, but i edited my thread, cause i dont need to compare 2 databased anymore, but 2 tables if there are same ids. would this query be correct? SELECT * FROM orderlinesold t1 LEFT JOIN orderlines t2 on t1.orderid = t2.orderid where t2.orderid is not null limit 30000; – JOP Mar 27 '13 at 21:14
  • No, you have to look for IS NULL in the table with fewer records. Assuming orderlines has more records than orderlinesold, then it should be: SELECT * FROM orderlinesold t1 RIGHT JOIN orderlines t2 on t1.orderid = t2.orderid where t1.orderid is null limit 30000; Note that I changed also LEFT to RIGHT. – koriander Mar 27 '13 at 21:33