Running a query on two databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running a query on two databases

Author

Topic

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 06:28:53

Hello all,
Can anyone please tell me what query to use for joining two tables in two databases together?

Basically, we have two databases called 'system' and 'site' respectively. The 'system' database has a table called 'users' and each user has a unique id which is also linked to the tables in the 'site' database.

The only problem i have got is, I want to be able to get the actual user names not the unique id and the only way to get this from my SQL statement is to be able to link the uniqueid for each user in the 'site' database to their corresponding uniqueid in the 'system' database.

Please, how can I do this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 06:31:06

If they are both on same server, you can refer them by prefixing database name in query:

Select ... from system.dbo.users u1 JOIN site.dbo.users u2 on ...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 06:39:20

quote:
Originally posted by harsh_athalye

If they are both on same server, you can refer them by prefixing database name in query:

Select ... from system.dbo.users u1 JOIN site.dbo.users u2 on ...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hi Harsh,
Thanks a lot. Perhaps if I show you the query I have currently built you might be able to help me. The query is:

Select wce_products.AMOUNT, wce_products.probability
From wce_products Inner Join
wce_sales On wce_products.recordmanager = wce_sales.recordmanager
Where wce_sales.createtime >= '2003/01/01'

All the tables in the above query are in the 'site' database but I need a column called 'username' in a table called 'users' which is in the 'system' database. How do I modify the above query?

Many thanks!

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 07:01:49

Please help me with this!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 07:05:51

I have already shown you.

select p.AMOUNT, p.probability, u.username
From wce_products p Inner Join
wce_sales s On p.recordmanager = s.recordmanager
JOIN system.dbo.users u on <join-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 07:44:25

Hi,
It doesn't seem to like system.dbo.users u it keeps complaining about that part

quote:
Originally posted by harsh_athalye

I have already shown you.

select p.AMOUNT, p.probability, u.username
From wce_products p Inner Join
wce_sales s On p.recordmanager = s.recordmanager
JOIN system.dbo.users u on <join-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 07:48:17

quote:
Originally posted by tomex1

Hi,
It doesn't seem to like system.dbo.users u it keeps complaining about that part

quote:
Originally posted by harsh_athalye

I have already shown you.

select p.AMOUNT, p.probability, u.username
From wce_products p Inner Join
wce_sales s On p.recordmanager = s.recordmanager
JOIN system.dbo.users u on <join-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

...what did you put in for the <join condition> ?
probably best if you post your actual query rather than Harsh's example, and include what the actual error was

Em

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 07:58:26

quote:
Originally posted by elancaster

quote:
Originally posted by tomex1

Hi,
It doesn't seem to like system.dbo.users u it keeps complaining about that part

quote:
Originally posted by harsh_athalye

I have already shown you.

select p.AMOUNT, p.probability, u.username
From wce_products p Inner Join
wce_sales s On p.recordmanager = s.recordmanager
JOIN system.dbo.users u on <join-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

...what did you put in for the <join condition> ?
probably best if you post your actual query rather than Harsh's example, and include what the actual error was

Em

Hi,
here's the actual query I am trying to run

Select wce_products.AMOUNT, wce_products.probability, wces_users.WCE_UID
From wce_products Inner Join
wce_sales On wce_products.recordmanager = wce_sales.recordmanager JOIN system.dbo.wce_system wces_users on wces_users.UNIQUEID = wce_sales.recordmanager
Where wce_sales.createtime >= '2003/01/01'

The error message I get is 'invalid object name system.db.wce_system'

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 08:10:02

quote:
Originally posted by tomex1

Hi,
here's the actual query I am trying to run

Select wce_products.AMOUNT, wce_products.probability, wces_users.WCE_UID
From wce_products Inner Join
wce_sales On wce_products.recordmanager = wce_sales.recordmanager JOIN system.dbo.wce_system wces_users on wces_users.UNIQUEID = wce_sales.recordmanager
Where wce_sales.createtime >= '2003/01/01'

The error message I get is 'invalid object name system.db.wce_system'

what's this...? (in red)

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 08:12:37

aha! i think you meant...


Select wce_products.AMOUNT, wce_products.probability, wces_users.WCE_UID
From wce_products Inner Join
wce_sales On wce_products.recordmanager = wce_sales.recordmanager JOIN wce_system.dbo.wces_users on wces_users.UNIQUEID = wce_sales.recordmanager
Where wce_sales.createtime >= '2003/01/01'

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 08:13:20

quote:
Originally posted by elancaster

quote:
Originally posted by tomex1

Hi,
here's the actual query I am trying to run

Select wce_products.AMOUNT, wce_products.probability, wces_users.WCE_UID
From wce_products Inner Join
wce_sales On wce_products.recordmanager = wce_sales.recordmanager JOIN system.dbo.wce_system wces_users on wces_users.UNIQUEID = wce_sales.recordmanager
Where wce_sales.createtime >= '2003/01/01'

The error message I get is 'invalid object name system.db.wce_system'

what's this...? (in red)

Em

That's the other database. It's called wce_system

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 08:16:34

yes i see that now. when you said you need to join to a table in the 'system' database, Harsh (and probably everyone that read your post) thought you meant a database called 'system' rather than wce_system

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 08:21:20

Are all the databases on same server?

Also, the error msg you posted is 'invalid object name system.db.wce_system'. Is it typo or you written db instead of dbo?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

tomex1
Starting Member

47 Posts

Posted - 2008-03-31 : 08:26:18

quote:
Originally posted by harsh_athalye

Are all the databases on same server?

Also, the error msg you posted is 'invalid object name system.db.wce_system'. Is it typo or you written db instead of dbo?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hi Harsh,
Yes, it's on the same server. Also, that was a typo and it's meant to be dbo.

Regards

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 08:40:29

I guess you are mixing up database name for table name.

This is the way it should be referred:

DATABASE-NAME.OWNER-NAME.TABLE-NAME

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources