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?
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