SQL query on joined tables in separate databases

[mitasol]

Hoping someone can point me in the right direction here. Please be gentle as I am still learning here.

I have 3 tables in 2 databases that I want to query - the result set is intended to be read only as it would be run through a case statement for totalling based on different criteria - ultimate output is a report or information form.

Table 1 is an audit table that contains transaction data including product_ID and customer_ID - this is in DATABASE1

Table 2 is a product table and table 3 is a customer table both tables are in DATABASE2

Now if I use MS-Access I can link the tables as externals and create a query with inner joins on product_ID and customer_ID from table 1 to table 2 but I can't see how this can be achieved in Lazarus. I have followed the SQL tutorials and am able to get a result set back from a single table and I think I can see how it would be done with multiple tables in a single database, but not with 2 databases.

I'd appreciate any assistance, feedback

regards

Scott

[Leledumbo]

Depends on backend dbms, for MySQL you can simply prefix the tables with database name. For others, read their respective documentation. Note that this implies the databases are hosted on the same host.

[mirce.vladimirov]

I can say for MySQL :
A connection to MySQL server gives you access to all databases located on that server. That means you can ue SQL code like this :

Code: [Select]

select col1, col2, col3... from db1.table1 join db2.table2 on ..... join db3.table3 on ......

FirebirdSQL provides one connection for one database so i think that the abbove query is not possible but you better read docs or ask a FBSQL user.

[mitasol]

Thanks for your responses,

it is Firebird - I suppose when I was linking to tables using MS Access it was MS Access that was being the backend which would be why I could run the query - I was just hoping to avoid having to use MS Access.

regards

Scott

[taazz]

http://www.firebirdfaq.org/faq16/