Multiple Join To Different DB

[alex_read]

I'm using MS SQL Server 2000 here & the plan is to connect 2 tables from database1 to the same table in database 2 Um, can i have some help to pick apart the below statement so it'll work please????

SELECT Table1.Col1,  
	   Table2.Col1 
FROM   Table1  
INNER JOIN DB2.TableA AS TBL1 ON Table1.KeyCol = TBL1.KeyCol
INNER JOIN DB2.TableA AS TBL2 ON Table2.KeyCol = TBL2.KeyCol
WHERE Table1.KeyCol = 1

[brucevde]

The full path to any SQL Server object is Server.Database.Owner.Object.

In your sample code DB2 would be the Owner. Simply add another period

INNER JOIN DB2..TableA

or specify the owner as well

INNER JOIN DB2.dbo.TableA

[alex_read]

Hi Bruce!

I tried adding DB2.dbo.Table1 before & have now tried with just the extra dot since you put that, but still no luck i'm afraid!

Thanks,
Alex

[vb_dba]

Does the user trying to join the table in the second database have access to the second database?

Are you getting an error message? Also, you reference a Table2, but it's not listed as a table in your From clause.

[brucevde]

Do you have both the Pubs and Northwind database on your server?

This query should work when logged into the Pubs database

Select fname, lname, db2.lastname
From Employee
Inner Join Northwind..Employees DB2 On db2.employeeid = Employee.job_id

[szlamany]

Whenever I am going to do cross database joins, I use the little trick in QUERY ANALYZER where you RIGHT CLICK on the TABLE, drag it into the QUERY pane and let go of the mouse. Choose SELECT from the drop-down menu.

Doing that gets you the fully qualified name of the database - one that is guaranteed to work.

[alex_read]

Hi Guys! Thanks for the help so far! Ok to take your sample bruce with the pubs/northwind databases, here's what i'm trying to do:

Select employee.fname, employee.lname,
jobs.job_id,
db2.lastname
From Employee
Inner Join Northwind..Employees DB2 On db2.employeeid = Employee.job_id
RIGHT OUTER JOIN Northwind..Employees DB3 on db2.employeeid = jobs.job_id

[alex_read]

Ok got it thanks! Here's the sort of thing i was trying to get. Many thanks again for those suggestions to help me find this! Much appreciated!

SELECT DB1_tbl1.[job_id], DB1_tbl2.[lorange]
FROM [pubs]..[jobs] AS DB1_tbl1
INNER JOIN [Northwind]..[Employees] AS DB2_tbl1 ON DB1_tbl1.[job_id] = DB2_tbl1.[employeeid]
LEFT OUTER JOIN [pubs]..[roysched] AS DB1_tbl2 ON DB2_tbl1.[employeeid] = DB1_tbl2.[lorange]