How to use multiple database adapter to query involving tables from different databases?

Question

I've 2 databases, which are set up as mentioned here. How can I write a SQL query which involves database_1.table_1 and database_2.table_1 ?

E.g. consider this query

$sql = "SELECT distinct database_1.users.id, database_1.users.name
        FROM database_1.users, database_2.sales
        WHERE database_2.sales.user_id = database_1.users.id";

How this query could be written using multiple db adapter?

Edit: I thought of using 2 database adapters because this way I can change actual database names in application.ini. Is there any other way I can change database names without changing sql queries?

Solution I'm using: I used another config variable to read second database name. First database name comes from adapter settings.

Answer 1

You can't unless you use something like Federated databases, but even then you'd use one database adapter for the query and let the database handle the fetching from the federated other databases.

Zend_Application_Resource_Multidb is just setting up multiple database adapters at once. Nothing more. You will use each adapter separately from the other. Use Adapter1 to query database1 and use adapter to query database2. Two queries. Merge the results inside your application.