Query using multiple DB's

Hi,

I was just wondering how I can execute a query using tables from different DB's.
I have access to both DB's, which reside on the same physical server.
When executing the query, I always got the following error:

MySQL Error: #1142 - SELECT command denied to user

Hoping someone can help me.

Thanks in advance

Well in that case you need more privileges to execute queries in the relevant database. Ask your administrator or whoever manages your MySQL privileges to do so.

I have access to both DB's.
I run other queries without any problem, but as soon as I want to join tables from both DB's, I got this error.
So, I don't see what other rights I should have or request.

did you prefix the table names with the appropriate db name followed by a dot ? (db.table)

Yes, all tablenames are fully prefixed.
Both DB's are opened using the session manager.
It seems that only 1 DB at a time can be used or do I need to change something in the preferences?

when you use dbname. prefixes, it should not even be necessary to open the second db. AFAIK, opening one db on the server (via explicit USE or selecting it in heidisql) should be enough.

Please post the results of

SHOW GRANTS FOR YourUserName

perhaps there is s subtle error in the privileges.

If you have one single connection to one MySQL server containing two database (DB_a and DB_b) following statement will work:

select DB_a.table_1.column_x, DB_b.table_2.column_y
from DB_a.table_1
join DB_b.table_2 on (DB_a.table_1.column_x = DB_b.table_2.column_y)

assumption: the join should makes sense... ;-)

Best regards,
Lutz

I think I've found the problem...
I have to use different usernames for these DB's, meaning username1 for DB1 and username2 for DB2.
As a result username1 doesn't exists on DB2 and vice versa.
For both usernames the result of SHOW GRANTS:

"GRANT USAGE ON *.* TO 'USERNAMEx'@'%' IDENTIFIED BY PASSWORD '...'"
"GRANT SELECT ON `MYDBx`.* TO 'USERNAMEx'@'%'"

So, is there a way to execute a query using 2 different usernames on 2 different DB's?

No. Your connection is based on a specific user. There is no way to bind a different user to a query. The solution is to extend the privileges of a user so it can execute what he needs.