CODEWITHSUNDEEP
mysqlsql-server
Suchi Johari
Suchi Johari

Reputation: 109

Select data from two different tables of two different database in SQL?

There are three tables 'TA' and 'TB' two belong to same database but the third table 'TC' belong to other database. I need to fetch the last row from the third table 'TC' and based on the data obtained in the last row i have to fetch all the data from 'TA' and 'TB'. Please help me for this as there are two database. Structures of the tables: TA(id, usrId) TB(fk_usrId, deviceId, version) TC(version) Need to select all the deviceId having latest version. Latest version is stored in the last row of the TC table.

Upvotes: 0

Views: 588

Answers (4)

G.Nagababu
G.Nagababu

Reputation: 43

select ta.id,ta.userid from ta join tb on ta.userid=tb.usrid join tc on tc.version=tb.version and tc.version=(SELECT TOP 1 * FROM tc ORDER BY version DESC)
here im selecting last column based on no then given desc based on ur need use the sub query.that must be unique

Upvotes: 1

N.Dinesh.Reddy
N.Dinesh.Reddy

Reputation: 632

First You need to add linked servers in server objects and then write the table name like [ServerInstanceName].[databasename].[schemaName].[TableName] for both tables follow the above syntax in the FROM Refference of query Rest of the code will be normal

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37059

Try this for SQL Server:

select ta.*, tb.*
from 
(select top 1 version from differentdb..tc order by version desc) t
inner join tb on tb.version = t.version
inner join ta on ta.id = tb.fk_usrId

Try this for MySQL:

select ta.*, tb.*
from 
(select version from differentdb.tc order by version limit 0,1) t
inner join tb on tb.version = t.version
inner join ta on ta.id = tb.fk_usrId

Upvotes: 0

mindbdev
mindbdev

Reputation: 444

In SQL Server if the two databases reside in the same server

SELECT * FROM FirstDB.Schemaname.TA INNER JOIN FirstDB.Schemaname.TC ON TA.userid = TB.user id and TB.version IN (SELECT TOP 1 version FROM SecondDB.Schemaname.TC ORDER BY version DESC)

In SQL Server if the two databases reside in different servers

Then you will have to add one server as a linked server in the other and use the server qualifier also in the above query

linked server reference link : https://msdn.microsoft.com/en-in/library/ms188279.aspx

Upvotes: 0

Related Questions