Hi all,
I need to lookup values from 2 database in a select field. kindly suggest me.
example:
select id,name from [db1].[ins0].[table1] inner join [db2].[ins0].[table2] on [table1].id=[table2].id1
where [table1].name={name}
Hello…
Try something like:
select TB1.id,TB2.name from db1.table1 as TB1 inner join db2.table2 as TB2 on TB1.id=TB2.id1 where TB1.name={name}
This, with MS SQL Server, works fine for me.
I suggest that you test the queries in a SQL query editor (Database.Net, for example), and then once it works, you parameterize it, and you take the SC
you cannot join tables from different databases
make sure your data.in one database,
or you need to run two queries and then do lookups
any way, where [table1].name={name}.
should be where [table1].name=’{name}’
it is text so it must be enclosed
Hello…
I can`t remember, in this moment, the project… but i use in some, and work…
In MySql \ MariaDB this should work everywhere a tablename can be used in a select statement:
dbname.tablename
That is if the two databases are running on the same DB server machine.
If the two databases are located on two separate DB server machines, I was still able to manage it in MariaDB using the Spider engine (where a table can be defined as a link to a db\table on a remote server).
No ideas if\how it can be done with RDBMS other than MariaDB.
This is a working Example:
SELECT Doc.COD_MEDICO,Doc.DESCRIPCION,doc.Archivo, Med.Email FROM dbo.TMPDocumentos AS Doc
INNER JOIN Ataner.dbo.Medicos AS Med ON Med.Cod_medico = Doc.COD_MEDICO
where COD_TMPDOC >= 238 ORDER BY Doc.COD_MEDICO
TmpDocumentos is in a Database (AtanerDocs) , and Medicos in other (Ataner)