Manually insert resultset into query

Question

I have one DB with managers and its dependent users. And the other db on other server, with some other information about users.

I want to query second DB and get data grouped by managers. One solution for this is to loop through result from db2 and calculate new result in application, but I would like to try to build result set from db1, with data about users and its managers to query to be executed on db2. Something like pseudo code below:

SELECT
b.manager_id,
SUM(a.column)
FROM user a
JOIN <resultset_with_assoc_between_user_and_manager> b ON a.user_id = b.manager_id
GROUP BY b.manager_id

Is this possible, without making any new tables in db2, just to make query with data from db1?

Have you checked out about linked servers? You can setup a link between these two servers so you will be able to query db2 through db1. [How to create linked server MySQL](http://stackoverflow.com/questions/5370970/how-to-create-linked-server-mysql) — Akalanka, Sep 09 '14 at 10:13
@Akalanka Yes, I chacked federated storage engine, but it is not an option. Because storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table, and my tables uses BRIGHTHOUSE engine, because of large number of columns and records. Aprox more than 3.5B records per table — dzona, Sep 09 '14 at 10:48

score 0 · Accepted Answer · answered Sep 09 '14 at 11:11

This is solution I made. It works fine, but I don't know what are performance penalties vs creating tmp table with user_id -> manager_id records, and joining them with db2_table . But as I said, db2_table is huuuuuuuge

SELECT

CASE
   WHEN a.user_id IN(user_id11, user_id12,user_id13, ...) THEN manager_id1
   WHEN a.user_id IN(user_id21, user_id22,user_id23, ...) THEN manager_id2
   WHEN a.user_id IN(user_id31, user_id32,user_id33, ...) THEN manager_id3
   ...
   ELSE a.user_id
END AS manager,
...

FROM db2_table a

GROUP BY manager

Manually insert resultset into query

1 Answers1