SQL query joining tables from different fdb databases
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
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:
- Create a temporary table, copy the data you need into that temporary table and then join to the temporary table,
- Merge the database into one.