inner join between database

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.