run a single query on multiple databases

Question

1)I have a table employee(emp_id, emp_name,emp_salary); I want to find top 2 employees having max salary without using limit

2)how do i run a query so that it takes records from databses on two database mysql servers this should be done in single mysql query

thanks in advance for any help or replies

Answer 1

So what exactly is wrong with the LIMIT?

select * 
  from (select * 
          from db1.employee
         order by salary desc limit 2 
        union 
        select * 
          from db2.employee
         order by salary desc limit 2
) emp
order by emp.salary desc limit 2

if employees in bd1 and db2 are certainly different or you dont want to remove duplicates change to UNION ALL

Answer 2

A single query can't talk to two database servers at once. It's just not allowed. But you can link the two servers using a FEDERATED table. This makes the "remote" table appear as if it's really stored locally.

However, then you're still stuck to having to use a union query, unless the two tables can be JOINed somehow.

SELECT blah,blah,blah
FROM localtable

UNION

SELECT blah,blah,blah
FROM federatedtable