What I did is:
mysql -uXXX -pYYY A
mysql> select * from B.region_xref where B.region_xref.main_id=20483
-> go
Empty set (0.00 sec) <==== Did not work across
database
mysql> use B
> Database changed
mysql> select * from B.region_xref where B.region_xref.main_id=20483
-> go <============= It works
from the same database
+---------+-----------+
| main_id | region_id |
+---------+-----------+
| 20483 | 3 |
| 20483 | 10 |
| 20483 | 1 |
| 20483 | 5 |
| 20483 | 7 |
| 20483 | 8 |
+---------+-----------+
Could I do cross-database queries?
Scott
Christian Mack wrote:
> Scott Liu wrote:
> >
> > I have tried the method to query tables in other database (B) when a
> > connection is made for
> > a database (A) as suggested in the "Re: join across databases". It did
> > not give me any error
> > nor it gave me any meaningful result. Any thought?
> >
> > Databases: A and B.
> > Table in B: region_xref
> >
> > mysql> use A
> > mysql> select * from B.region_xref where B.region_xref.main_id=20483
> > -> go
> > Empty set (0.00 sec) <==== Did not work
> > across database
> >
> > mysql> use B
> > > Database changed
> > mysql> select * from B.region_xref where B.region_xref.main_id=20483
> > -> go <============= It works
> > from the same database
> > +---------+-----------+
> > | main_id | region_id |
> > +---------+-----------+
> > | 20483 | 3 |
> > | 20483 | 10 |
> > | 20483 | 1 |
> > | 20483 | 5 |
> > | 20483 | 7 |
> > | 20483 | 8 |
> > +---------+-----------+
> >
> > The original email on the "Re: join across databases":
> > >
> > > "Subject:
> > > Re: join across databases?
> > > Date:
> > > Wed, 5 May 1999 21:45:14 -0700
> > > From:
> > > Thimble Smith <tim@stripped>
> > > To:
> > > Daevid Vincent <daywalker@stripped>, mySQL database
> > > <mysql@stripped>
> > > References:
> > >
> > > On Wed, May 05, 1999 at 09:36:25PM -0700, Daevid Vincent wrote:
> > > > Is it possible to join across databases?
> > >
> > > Yes, it is. You can find the information in the manual. In short,
> > > you prefix the table name with the database name:
> > >
> > > SELECT l, p FROM db1.tableA, db2.tableB WHERE db1.tableA.m >
> > > db2.tableB.q
> > >
> > > Tim"
> >
> > Thanks,
> >
> > Scott Liu
>
> Hi Scott
>
> Sorry, couldn't reproduce your problem :(
>
> Can you provide a full example?
>
> Tschau
> Christian
--
Scott Liu
Software Developer
831 460 4300
.....................................................................
TAPESTRY.NET - On Target, Online Recruiting
111 Mission St. Santa Cruz, CA 95060
Visit our web sites at:
http://www.tapestry.net
http://www.asia-net.com Bilingual Professionals
http://www.developers.net Software Developers
