CREATE TABLE ORA (
    ID               DM_ID NOT NULL /* DM_ID = INTEGER NOT NULL */,
    CH_ID);

CREATE TABLE ORB (
    ID         DM_ID NOT NULL /* DM_ID = INTEGER NOT NULL */,
    ORA_ID     DM_ID /* DM_ID = INTEGER NOT NULL */);

ALTER TABLE ORB ADD CONSTRAINT FK_ORB_1 FOREIGN KEY (ORA_ID) REFERENCES ORA (ID) ON DELETE CASCADE;

select a.id
from ora a
join orb b on b.ora_id = a.id
where a.ch_id = :ch_id

select a.id
from ora a
join orb b on b.ora_id = a.id
join orb bb on bb.ora_id = a.id
where a.ch_id = :ch_id

CH_ID            DM_ID /* DM_ID = INTEGER NOT NULL */