Thread: inner join between database
Can I use Inner join two tables between two databases? if possible, how to write this SQL? If not, any other way to accomplish it? thank you!
On Thu, Jul 11, 2002 at 10:34:01 +0900, "Huang@tanpopo-tane.com" <huang@tanpopo-tane.com> wrote: > Can I use Inner join two tables between two databases? You cannot do that. > if possible, how to write this SQL? If not, any other way to accomplish it? > thank you! An application can talk to both databases so it could do the join or it could copy the data from one database to a temporary table in the other and do the join there. You coulkd also consider doing a redesign and combining the two databases.
Bruno Wolff III a écrit: > "Huang@tanpopo-tane.com" <huang@tanpopo-tane.com> wrote: > >Can I use Inner join two tables between two databases? > >if possible, how to write this SQL? If not, any other way to accomplish it? >thank you! the shorter I found was to "select into" a temporary table which means combining 2 queries and do the inner join on it
> > > Can I use Inner join two tables between two databases? > > You cannot do that. > Ooops! Does that mean there is no way to access data from another database within the *same* cluster? That would cause me some trouble because I plan to give each user his own database (PG does not support SCHEMAs). Is there no way to make a single table available to all users? Christoph Dalitz
On Thu, 2002-07-11 at 13:37, Christoph Dalitz wrote: > > > > > Can I use Inner join two tables between two databases? > > > > You cannot do that. > > > Ooops! > Does that mean there is no way to access data from another > database within the *same* cluster? Correct. There is no communication between databases. > That would cause me some trouble because I plan to give each > user his own database (PG does not support SCHEMAs). > Is there no way to make a single table available to all users? Schemas will be implemented in 7.3. Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "I waited patiently for the LORD; and he inclined unto me, and heard my cry. He brought me up also out of an horrible pit, out of the miry clay, and set my feet upon a rock, and established my goings. And he hath put a new song in my mouth, even praise unto our God. Many shall see it, and fear, and shall trust in the LORD." Psalms 40:1-3
On Thu, Jul 11, 2002 at 14:37:28 +0200, Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> wrote: > > > > > Can I use Inner join two tables between two databases? > > > > You cannot do that. > > > Ooops! > Does that mean there is no way to access data from another > database within the *same* cluster? I am not sure what you mean by cluster in this context. However, separate databases cannot have direct references to each other. This is on the todo list, but I haven't seen messages on the lists indicating this will be done anytime soon. > That would cause me some trouble because I plan to give each > user his own database (PG does not support SCHEMAs). Schema support will be in 7.3 which is supposed to go into beta in August sometime. The last comment I noticed regarding the current status is that playing with it (current CVS) on production servers was not a good idea. However, if you are developign something now that doesn't need to be deployed for a few months, than maybe it would be worth looking at this option. > Is there no way to make a single table available to all users? I don't think this is really the question you want answered. You certainly can have a table in a database and give all of your users access to that database and that table. However they won't be able to use that table directly with data located in their private databases. Depending on how this data is created and used, there may be acceptable practical work arounds.