SQL query joining tables from different fdb databases

Question

I have 2 fdb company.fdb and timeAtt.fdb

company.fdb contains the staffDetail table

 staffId - 001 staffName - Andy staffStatus - Active 

timeAtt.fdb contains a 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 look at the staff who was absent just that I did it this way

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

But the problem is that the request also displays inactive personnel. Therefore, I need to join staffAtt from timeAtt.fdb and staffDetail from company.fdb to display only employees with active status. How can i do this?

Answer 1

You can not. In Firebird, you can only join tables in a single database file. Firebird 2.5 extended EXECUTE STATEMENT to execute the statement on an external data source, but it is not possible to have one query link table in different databases.

You have the following options:

• Create a temporary table, copy the necessary data into this temporary table and then join the temporary table,
• Combine the database into one.

Answer 2

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

Use execute block and execute statement together. Here is 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