0

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

1
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: ...

  • 1
    DISTINCT is unnecessary. The default behaviour of the UNION set operator is to return de-duplicated results. – Francois Sep 8 '15 at 10:23
  • True. But I like to explicitly say that I thought about whether to use ALL (which is faster) or DISTINCT (which adds a de-dupping pass). – Rick James Sep 8 '15 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.