Accessing tables in different database

Question

Seems like a trivial question, but just doesnt work for me. I need to get data from db A (news) and check it against db B (news-backup). They are located in same place, and dont need authenticating (i think at least). It gives me an error 1146, table "news.news" doesnt exist. when I try to access on DB with the 2nd

SELECT B.`comment`
FROM news A, `news-backup`.`data` B
WHERE B.source = A.rss_atom.id and B.feed = "rss-atom"

rss_atom and data are tables

score 2 · Accepted Answer · answered Dec 10 '12 at 15:37

2

You need to specify the database name and table in your FROM clause, try this:

SELECT B.comment
FROM news.rss_atom A, news-backup.data B
WHERE B.source = A.id AND B.feed="rss-atom"

I hope I got your table structure correct.

answered Dec 10 '12 at 15:37

Ruben_PH

1,692
7
25
42

yea i screwed by changing DBs i use and try to get data from, thanks! – rodling Dec 10 '12 at 15:43

score 1 · Answer 2 · edited May 23 '17 at 11:48

They are in different databases so, unless you specify the change with use database A (or B), you can't see all the objects. Also, you could try setting the database name as prefix to the tables you're querying IF the databases could be available thru the same connection and the user connecting was granted enough rights to query tables on both databases.

This answer could be helpful. How do you connect to multiple MySQL databases on a single webpage?

score 1 · Answer 3 · answered Dec 10 '12 at 15:39

SELECT B.`comment`
FROM `news`.`rss_atom` A, `news-backup`.`data` B
WHERE B.source = A.rss_atom.id and B.feed = "rss-atom"

at a guess, if you are using the news database then

SELECT B.`comment`
FROM `rss_atom` A, `news-backup`.`data` B
WHERE B.source = A.rss_atom.id and B.feed = "rss-atom"

No table news in news database was a decent clue.

Accessing tables in different database

3 Answers3