Suggestions on retrieving related data across databases with different logins?

Question

I have an array of user ids in a query from Database A, Table A (AA).

I have the main user database in Database B, Table A (BA).

For each user id returned in my result array from AA, I want to retrieve the first and last name of that user id from BA.

Different user accounts control each database. Unfortunately each login cannot have permissions to each database.

Question: How can I retrieve the firsts and lasts with the least amount of queries and / or processing time? With 20 users in the array? With 20,000 users in the array? Any order of magnitude higher, if applicable?

Using php 5 / mysql 5.

Answer 1

As long as the databases are on the same server just use a cross database join. The DB login being used to access the data will also need permissions on both databases. Something like:

SELECT AA.userID, BA.first, BA.last
FROM databasename.schema.table AA
INNER JOIN databasename.schema.table BA ON AA.userID = BA.userID

In response to comments:

I don't believe I read the part about multiple logins correctly, sorry. You cannot use two different mySQL logins on one connection. If you need to do multiple queries you really only have three options. A) Loop through the first result set and run multiple queries. B) Run a query which uses a WHERE clause with userID IN (@firstResultSet) and pass in the first result set. C) Select everything out of the second DB and join them in code.

All three of those options are not very good, so I would ask, why can't you change user permissions on one of the two DBs? I would also ask, why would you need to select the names and IDs of 20,000 users? Unless this is some type of data dump, I would be looking for a different way to display the data which would be both easier to use and less query intensive.

All that said, whichever option you choose will be based on a variety of different circumstances. With a low number of records, under 1,000, I would use option B. With a higher number of records, I would probably use options C and try to place the two result sets into something that can be joined (such as using array_combine).

Answer 2

I think they key here is that it should be possible in two database calls.

Your first one to get the id's from database A and the second one to pass them to database B.

I don't know mysql, but in sqlserver I'd use the xml datatype and pass all of the ids into a statement using that. Before the xml datatype I'd have built up some dynamic SQL with the id's in an IN statement.

SELECT UserId FROM DatabaseA.TableA

Loop through id's and build up a comma separated string.

"SELECT FirstName, Surname FROM DataBaseB.TableA WHERE UserId IN(" + stringId + ")"

The problem with this is that wth 20,000 id's you may have some performance issues with the amount of data you are sending. This is where'd I'd use the XML datatype, so maybe look at what alternatives mysql has for passing lists of ids.