CODEWITHSUNDEEP
mysql
Sarath
Sarath

Reputation: 89

How to get a report by comparing MySQL tables in two different Databases

I have two Mysql database dumps (DB1_10.10.2019 and DB2_10.02.2020). I created a custom query in DB1_10.10.2019 for getting the user details and made a 'report1'.

DB1_10.10.2019:

SELECT DISTINCT(...)
FROM ...
WHERE...
ORDER BY ...

For now, I have created a table called Table1 and stored the results.

INSERT INTO Table1(...,...,...)
 SELECT DISTINCT(...)
 FROM ...
 WHERE...
 ORDER BY...

Let's say after 4 months I want to get the user details, but this time I do not want users in report1. I want only new users. Can anyone please suggest a solution on how to achieve this?

I referred to the following site: https://www.mysqltutorial.org/mysql-minus/ and tried the following method:

SELECT 
    id
FROM
    t1
LEFT JOIN
    t2 USING (id)
WHERE
    t2.id IS NULL;

The above query works only if we have two tables in the same DB. But in my case, I have two DB's.

MySQL version: 5.7.19

Upvotes: 1

Views: 40

Answers (1)

Alberto Moro
Alberto Moro

Reputation: 1013

If the database is on the same machine and your user can access both, just enter the database name in the query as follows:

SELECT 
    `DB1_10.10.2019`.t1.id
FROM
    `DB1_10.10.2019`.t1
LEFT JOIN
    `DB2_10.02.2020`.t2 USING (id)
WHERE
    `DB2_10.02.2020`.t2.id IS NULL;

Here you can find some examples

Upvotes: 1

Related Questions