Use Two Different Databases in a Query

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

General SQL Server Forums

New to SQL Server Programming

Use Two Different Databases in a Query

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-09 : 15:05:29

I have the need to pull a column FULL_NAME from table USERS in database A based on USERID in table USERS in database B.

So say I need to create a list that looks like this:

Joe Smith 123
Jane Doe 456
John Jones 789

... where the user names come from database A and the user id's (the numbers) come from database B.

What would a single SQL statement that would produce this look like?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-09 : 15:18:49

When you say "based on USERID" that implies that the [userid] column is in both tables. So why can't you get full_name and userid from the same table?

EDIT:
but here is an example of joining tables from two databases. You simply qualify the table name: <db>.<owner>.<tableName>


select a.full_name
       ,b.userid
from   databaseA..[Users] a
inner join databaseB..[Users] b
       on b.userid = a.userid

Be One with the Optimizer
TG

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-09 : 15:31:18

Good question. This is custom code within a 3rd party application. The 3rd party provides a way to do custom reports using SQL against a project (a project = a database). The user can setup multiple projects, each project having its own database. Each project database has ONLY the userid but not the user name. The site database holds the userid AND user name.

So, within a single project where access via sql is only against that project's database, the user's full name is not available. In an ad hoc sql command processor window in the project, the site database can be accessed by first executing the command "use siteadmin_db". Once that command is executed, I have access to the tables in the site admin database.

The problem is, I need to write a single query against the project db that pulls, amongst other data, the userid but in the output I want to include the user's full name, not just the user id. The user's full name is in the site admin db. The userid is what's in common between the two db's.

Within the confines of what the application provides in terms of sql processing, it necessary to use two different databases in a single query if its even possible. If its not possible, I just tell the person that requested the report that it can't be done and they live with user id only.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-09 : 15:35:10

ok - then it sounds like the example I provided (above) should do the trick for you. Does it?

Be One with the Optimizer
TG

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-13 : 08:55:21

I'm not able to get the query to run ... I get an invalid column name error on the second occurence of BG_DETECTED_BY but BG_DETECTED_BY is *definitely* a valid column name ... Here is the sql statement:

SELECT A.FULL_NAME, B.BG_DETECTED_BY FROM QCSITEADMIN_DB..[USERS] A INNER JOIN DCW_TESTBUILDMASTER_DB..[USERS] B ON B.BG_DETECTED_BY = A.USER_NAME

Note: Did I construct this right? I'd never seen ".." used in a query and in the example provided ".." was used - is that supposed to be "..." (elipses) that indicate something is supposed to be filled in? If so, I couldn't figure out what.

Any ideas?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-13 : 10:12:40

The ".." is correct and your statement appears fine. FYI, the ".." is when you qualify the table name with the database name but leave off the owner. <db>.<owner>.<table> The owner is optional so you can say: <db>..<table>

Let's confirm you are using MS Sql Server, and then run these two statements:
select top 1 * from QCSITEADMIN_DB..[USERS] A --make sure the [a] table has [User_name] and [Full_name]
select top 1 * from DCW_TESTBUILDMASTER_DB..[USERS] B --make sure the [b] table has [BG_Detected_by]

Be One with the Optimizer
TG

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-13 : 10:14:01

Got it ... stupid mistake ... wrong table name s/b DCW_TESTBUILDMASTER_DB..[BUG] ... NOT [USERS]

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-13 : 10:15:03

.. is fine.

[/fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-13 : 10:16:24

School Boy error, we all make them. Do not worry my lad. (Only Madhivanan / khtan do not makes errors, ever. Full Stop.)

[/fail at query]