SQL query joining tables from different fdb databases

Question

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?

Answer 1

As Mark notes you cannot join them directly. But you can still use a DSQL statement to get what you want.

Use execute block and execute statement together. Here's a sample.

execute block
returning (
   staffId integer,
   staffName varchar(100),
   remarks varchar(100)
   staffStatus varchar(10))
as
begin
   for SELECT staffId, staffName, remarks 
   FROM timeAtt 
   WHERE AB = 'X'
   into :staffId, :staffName, :remarks do begin

      execute statement 'select staffStatus from company where staffId = ' || staffId
      on external "your:connection:\string\and\db.fdb" as user FOO password BAR
      into :staffStatus;

      suspend;
   end
end

Answer 2

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.