sqlfirebirdjoinfirebird2.1

SQL query joining tables from different fdb databases

I have 2 fdb databases company.fdb and timeAtt.fdb

company.fdb contains staffDetail table

staffId       - 001
staffName     - Andy
staffStatus   - Active

timeAtt.fdbcontains staffAtt table

staffId         - 001
staffName       - Andy
timeIn          - 07:30
timeOut         - 04:30
LI              - X (late in)
AB              - X (absent )
remarks         - Emergency leave

Now, i would like to view the staff who was absent only which i did it this way

SELECT staffId,staffName,remarks FROM timeAtt.fdb WHERE AB = 'X'

But the problem is, the query also display inactive staff. So i need to join staffAtt from timeAtt.fdb and staffDetail from company.fdb to display only staff with active status. How can i do that?

Solution

  • You can't. In Firebird you can only join tables in the same database file. Firebird 2.5 expanded EXECUTE STATEMENT to also execute a statement on an external datasource, but having a single query reference tables in different databases is not possible.

    You have the following options:

    1. Create a temporary table, copy the data you need into that temporary table and then join to the temporary table,
    2. Merge the database into one.