Easy one: SELECT on multiple Databases

[melginger]

I can't make it works. I'm in the Query analyzer and I want to do a SELECT statement on two tables FROM two different Databases on the same SQL server.

I found something like

SELECT TA.field1, TB.field1, TA.field2 FROM
DB1.dbo.T1 as TA INNER JOIN DB2.dbo.T2 as TB ON
TA.field1=TB.field1

But I can't make it works. Where do I run that query, on Master? How do I syntax it?

It's surely an easy one but I'm blocked, so thank you for your help.

 

[SQLBill]

First, do your two tables have anything in common? In the example you use, Field1 of TableA and Field1 of TableB have common information. For example: social security numbers.

Next, if possible provide us with the schema of the two databases (Table names and field names). Then we can provide better help in doing what you want.

-SQLBill

Posting advice: FAQ481-4875

 

[melginger]

Finally, I created a view of the other database table in my principale database. Now I can join the two.

Thanks anyway

 

[JamesLean]

There should be no reason to have to work around it by creating a view - as you say this is quite a simple technique.

Your example query looks fine. If you post the error you are getting I'm sure we can work out what's going on.

--James

 

[chamilz]

Try this!

SELECT TA.field1, TB.field1, TA.field2 FROM
"DB1".dbo.T1 as TA INNER JOIN "DB2".dbo.T2 as TB ON
TA.field1=TB.field1

/Chamil

 

[chamilz]

sorry it should be changed...

SELECT TA.field1, TB.field1, TA.field2 FROM
"server".DB1.dbo.T1 as TA INNER JOIN "server".DB2.dbo.T2 as TB ON
TA.field1=TB.field1
/Chamil

 

[SQLBill]

Chamil,

The poster doesn't need to add the 'server' portion as both databases are on the same server.

I have a feeling the two databases don't have a field in common and the ON might not be working. But without the actual error and knowledge of the tables and column names, it's hard to help with this problem.

-SQLBill

Posting advice: FAQ481-4875

 

[chamilz]

SQLBill,

You are right..
i did not read the second sentence properly.
Thanks..Chamil