CODEWITHSUNDEEP
sqlsql-server
vivek kumar luetel
vivek kumar luetel

Reputation: 137

How to join two tables if they are in different schemas

I have two different schemas in SQL Server (say S1, S2). And two tables in those schemas(say S1.Table1, S2.Table2). I want to query these two tables from schema S1.

Both S1 and S2 are in SQL Server 2005 databases. I want to do something like this:

select T1.Id
  from S1.Table1 T1
     , S2.Table2 T2 
 Where T1.Id = T2.refId

Upvotes: 11

Views: 79494

Answers (3)

Anthony
Anthony

Reputation: 79

Select T1.Id

FROM

s1.Table1 T1

JOIN

s2.Table2 T2

WHERE

T1.Id = T2.refId;

This is the way to do your query on MySQL. I would assume it also works in Oracle 11g.

Upvotes: 4

M.R.
M.R.

Reputation: 4837

You didn't mention if the DBs were on the same server. If yes, you can follow the answer above. If not, you will need to create a linked server one of the servers, and then you can reference the linked server via


select T1.Id
  from [linkedservername].DB1.Schema.Table1 T1
     , S2.Table2 T2 
 Where T1.Id = T2.refId

Upvotes: 3

gbn
gbn

Reputation: 432431

Use 3 part object names to specify the database: I assume you mean "database" not "schema" (in say the Oracle sense)

select T1.Id
from 
  DB1.schema.Table1 T1
 JOIN
   DB2.schema.Table2 T2 ON T1.Id = T2.refId

Note the better way of doing JOINs...

Upvotes: 9

Related Questions