> select distinct c.*, a.*
> from (((ch_daten c LEFT JOIN auftrags_daten_run a ON c.objid =
> a.objid)
> LEFT JOIN wb_prg_run w ON c.objid = w.objid)
> LEFT JOIN pruefdaten_run p on a.objid=p.objid and a.nr=p.sub_id)
> INNER JOIN (
> cmd cmdm INNER JOIN ch_daten chdm ON (cmdm.objid=chdm.objid and
> cmdm.typeofcmd=13)
> ) ON (c.objid=cmdm.to_objid)
> where
> chdm.dch_nr=70106234

Why all these parenthesis, Joja? You have specifically told it to left join ch_daten to autrags_daten_run before joining to ch_daten and leaves the optimizer with less choices. I'm not saying that an optimal optimizer couldn't generate the plan you want, but you are making it more difficult for it to do so, and in this case Firebird gets it wrong. To make things simpler for the optimizer, I always try to put LEFT JOIN towards the end of my JOIN clause and don't use parenthesis unless I have a very good reason to do so (I don't remember whether I've ever had any such reason, but I think I have seen queries where it helped).

So, I recommend you to change to

select distinct c.*, a.*
from ch_daten c
INNER JOIN cmd cmdm ON c.objid=cmdm.to_objid
INNER JOIN ch_daten chdm ON cmdm.objid=chdm.objid and cmdm.typeofcmd=13
LEFT JOIN auftrags_daten_run a ON c.objid = a.objid
where
chdm.dch_nr=70106234

(I also removed the two left joined tables that you're not selecting from since they do not contribute to your answer in any way.)

and report back the plan if it still isn't optimal.

HTH,
Set


[Non-text portions of this message have been removed]