CODEWITHSUNDEEP
mysql
rodling
rodling

Reputation: 998

Accessing tables in different database

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

Upvotes: 0

Views: 113

Answers (3)

Alfabravo
Alfabravo

Reputation: 7579

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?

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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.

Upvotes: 1

Ruben_PH
Ruben_PH

Reputation: 1812

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.

Upvotes: 2

Related Questions