CODEWITHSUNDEEP
sql-serverstored-procedures
ghost_king
ghost_king

Reputation: 930

Join 4 tables across 2 databases

TB1, TB2, TB3 are in one database on the server and TB4 is on another database.

The column information needed for the join are as follows:

TB1, TB2, TB3 are related by ID TB1's ID is the primary key and the foreign keys in TB2, TB3 and TB4

I am using SQL Server Management Studio

Code:

select ID from TB1, TB2, TB3 where TB1.ID = TB2.TB1.ID

Upvotes: 1

Views: 174

Answers (1)

aF.
aF.

Reputation: 66697

You can link them like this (don't forget to put the relation that you missed to say):

SELECT t1.a1, t1.b2, t2.a2, t2.b2, t2.c3, t3.a3, t4.a4
FROM db1..TB1 t1
INNER JOIN db1..TB2 t2 ON t1.ID = t2.ID
INNER JOIN db1..TB3 t3 ON t1.ID = t3.ID
INNER JOIN db2..TB4 t4 ON --put relation here

Also replace db1 and db2 by the database names on which each table is present.

One final note, your code it's giving redundant information because you didn't make all the restrictions. So, a cartesian product is produced.

Upvotes: 2

Related Questions