Re: query to join two tables in 2 different schema

Just two different schemas but in the same database correct?

You just have to prefix the schema names to the table names. Something like:

select s.study_id, r.report_type
from schema_A.study s

     schema_B.report s
where s.study_id = r.study_id

Of course the user doing the query should have been granted the necessary privileges for those tables.

Do check out the Oracle documentation set:

http://tahiti.oracle.com

The Concepts Guide, SQL, PL/SQL manuals are just some readings to get you started.

Cheers!

harryooopotter_at_hotmail.co_ (Harry) wrote in message news:<8jg9d.38956$N%.37002_at_edtnps84>...
> At my work we have an Oracle 9 database sitting on a W2K server.
> Therre are two different schema, one for overall info and the other
> just for storing reports.
>
> schema A: a table "study" with study_id, etc.
>
> schema B: a table "report" with study_id, etc.
>
> How can I made a query, in SQL Plus (or PL/SQL, or Perl script)
> that can join the table together?
>
> pseduo code:
>
> select s.study_id, r.report_type
> from study s connected to schema A using userid xxx password yyy,
> report s connected to schema B using userid ppp password qqq
> where s.study_id = r.study_id
>
> Is it doable?
>
> TIA
Received on Thu Oct 07 2004 - 20:30:23 CDT