INNER JOIN table on another DB help!

[Laeg]

DB1.tblFoo
FooID | FooValue | BarID

DB2.tblBar
BarID | BarValue

How do I INNER JOIN on tblBar

SELECT *
FROM DB1.tblFoo a
INNER JOIN DB2.tblBar b on a.BarID = b.BarID

if they have different username/passwords for each database. How do I authenticate? I can do this in a stored procedure if needs be.

 

[TonyGroves]

A query can only access one database server at a time, the server associated with the authenticated connection. Are both your databases located on the same server? If yes, then you'll have no problems. If they are on separate servers, then you'll have to find a workaround, such as writing a program to query both servers separately and merge the results, or to copy data from one database to the other so that you can then run your query.

 

[Laeg]

They are both on the same server luckily, how would I go about it? I was thinking of creating the same user in both databases?

 

[ingresman]

Most languages that support an API style connection should be ok.
Certainly PHP will let you have more than one database open and so will an ADO/ODBC style connection from something like VBscript under windows.
If you feel brave check out things like Access which allow you to link access tables to server tables. So you could like tableA to one db and tableB to another and issue queries like select <?> from tableA, tableB where ...
Or if you want a free route try out the expression version of SQLServer which I think lets you define remote servers via ODBC and issue single queries from there. Best to ask in the SQLSerer forum for that though.

 

[TonyGroves]

You wouldn't normally set up user access controls for each database, unless you need to do it for security reasons. Check out the GRANT statement, which allows you to set up access controls for the server and for individual databases and tables. You can also edit the user tables manually using SQL. Once you have that sorted out, then you can fire ahead with cross-database queries just like the example you gave.