MySQL add current db as prefix automatically

Question

Say I have two databases db1 and db2, and I have full privileges to access them.
But when I issue the following sql:

SELECT * FROM `db1.tbl1` AS t1 JOIN `db2.tbl2` AS t2 ON t1.id=t2.id

I get this error:

Table `db1.db1.tbl1` doesn't exist.

db1.tbl1 does exist. It seems mysql automatically add database name as prefix. What am I doing wrong?

score 4 · Accepted Answer · answered Dec 25 '13 at 09:10

You should backtick quote the database name and table name separately:

SELECT * FROM `db1`.`tbl1` AS t1 JOIN `db2`.`tbl2` AS t2 ON t1.id=t2.id

Or just without backticks if there is no reserve name.

SELECT * FROM db1.tbl1 AS t1 JOIN db2.tbl2 AS t2 ON t1.id=t2.id

score 0 · Answer 2 · answered Dec 25 '13 at 09:09

0

You need to do this:

SELECT * FROM `db1`.`tbl1` AS t1 JOIN `db2`.`tbl2` AS t2 ON t1.id=t2.id;

You need to add the ` till with the both table name and database name.

answered Dec 25 '13 at 09:09

Code Lღver

1

I don't think so. http://stackoverflow.com/questions/5698378/mysql-join-between-tables-in-2-different-databases – Cthulhu Dec 25 '13 at 09:09
1

You mean I can't join two tables from two different databases? I don't think so... – adamsmith Dec 25 '13 at 09:10

2 Answers2