Mysql – Select not repeated results from same tables in different databases

mariadbMySQLselect

I have 3 tables in 3 different databases which are exactly the same. I'm trying to sync from this 3 databases to a master database as shown in the picture below:

enter image description here

Right now I am running the same script (made in PHP) and changing all the time the slave database (in the figure db1, db2, db3 act as slaves) and I want to change that. I have the following query that returns data from user table:

SELECT
  u.id AS uid,
  u.username AS username,
  u.firstname AS ufirstname,
  u.lastname AS ulastname,
  u.email AS uemail,
  u.isactive AS uisactive,
  u.lastmodifieddate AS ulmd,
  t.id AS tid,
  t.name AS tname,
  t.lastmodifieddate AS tlmd,
  ut.lastmodifieddate AS utlmd
FROM `territory` AS t 
JOIN `userterritory` AS ut ON (ut.territoryid = t.id) 
JOIN `user` AS u ON (ut.userid = u.id)
WHERE u.lastmodifieddate > some_date_value 
   OR ut.lastmodifieddate > some_date_value
   OR t.lastmodifieddate > some_date_value

But as I said before I want to run the same query in the 3 databases at the same time and not get repeated results? Any help?

There are a few tables included on the query as you may notice but think that 3 databases are exactly the same and can share different or the same data in some cases so use or don't the tables as reference on your answer. DB's are running on the same server and share the same credentials

Best Answer

SELECT ... FROM db1.user
UNION DISTINCT
SELECT ... FROM db2.user
UNION DISTINCT
SELECT ... FROM db3.user
;

The DISTINCT will de-dup based on what you have in the select: ...

Related Question