Cannot access table in 2nd database from MySQL query

Question

I have 2 databases on a server:

I am trying to query a table in each database. However, when I try that, I get the following error:

Unknown table 'database2.client' in field list

Here is the code that I am using in a script running on database 1:

SELECT database2.client.id;

It returns the unknown table error. The odd thing is that if I query for the databases, it shows them both:

SHOW DATABASES

it returns the following:

information_schema
database1
database2

I'm not sure why the SHOW syntax confirms that both databases are there, but I'm not able to select data from the other table.

Does the user under which you are running the queries on each database server? — JSK NS, Oct 03 '12 at 17:17

gcochard · Answer 1 · 2012-10-03T17:32:48.197

1

Can you select it if you first run use database2, and then SELECT client.id?

EDIT:

As many people have pointed out, you need to use the correct SELECT syntax.

USE database2;
SELECT id FROM client;

edited Oct 03 '12 at 17:32

answered Oct 03 '12 at 17:17

gcochard

this works but only with "SELECT id FROM client" - not SELECT client.id – Daniel C Oct 03 '12 at 17:31

score 1 · Answer 2 · answered Oct 03 '12 at 17:17

1

Try this:

SELECT id FROM database2.client;

answered Oct 03 '12 at 17:17

JSK NS

score 1 · Answer 3 · answered Oct 03 '12 at 17:18

1

I believe the correct syntax would be:

SELECT id FROM database2.client

Where id is the column, database2 is your database, and client is your table.

answered Oct 03 '12 at 17:18

Kermit

3 Answers3