<class table="brokers" name="Counterparty" mutable="false">
    <id name="code" type="integer" column="broker_code"/>   
    <set name="desks" fetch="subselect" lazy="false">
        <key column="broker_code"/>
        <one-to-many class="Desk"/>         
   </set>   
   <set name="settlementInstructions" fetch="subselect" lazy="false">
        <key column="broker_code"/>
        <one-to-many class="SettlementInstruction"/>
   </set>
</class>

<class table="sub_brokers" name="Desk" mutable="false">
    <composite-id name="id"  class="Desk$Id">
        <key-property name="brokerCode" type="integer" column="broker_code"/>
        <key-property name="subBrokerCode" type="string" column="sub_broker_code"/>
     </composite-id>   
     <property name="entity" type="integer" column="entity"/>
</class>

<class table="broker_routing" name="SettlementInstruction" mutable="false">     
    <id name="settleCode" type="integer" column="settle_code"/>       
    <property name="brokerCode" type="integer" column="broker_code"/>
    <property name="entity" type="integer" column="entity"/>
  </class>

Table: brokers         
broker_code
1
2
3

Table: sub_brokers
broker_code   sub_broker_code       entity
1                    a                  1
1                    b                 -1



Table: broker_routing
settle_code      brokerCode       entity
5                    1             1
6                    1            -1

select distinct b from Counterparty b  join fetch b.desks d left join fetch b.settlementInstructions s where d.entity >= 0  and s.entity >= 0

select distinct counterpar0_.broker_code as broker1_0_, desks1_.broker_code as broker1_1_,
        desks1_.sub_broker_code as sub2_1_, settlement2_.settle_code as settle1_2_,
        desks1_.entity as entity1_1_, desks1_.broker_code as broker1_0__,
        desks1_.sub_broker_code as sub2_0__, settlement2_.broker_code as broker2_2_2_,
        settlement2_.entity as entity2_2_, settlement2_.broker_code as broker2_1__,
        settlement2_.settle_code as settle1_1__
from externdb.dbo.brokers counterpar0_
inner join externdb.dbo.sub_brokers desks1_ on counterpar0_.broker_code=desks1_.broker_code
left outer join externdb.dbo.broker_routing settlement2_ on counterpar0_.broker_code=settlement2_.broker_code
where desks1_.entity>=0 and settlement2_.entity>=0

select distinct b from Counterparty b  join b.desks d left join b.settlementInstructions s where d.entity >= 0  and s.entity >= 0

select distinct counterpar0_.broker_code as broker1_
from externdb.dbo.brokers counterpar0_
inner join externdb.dbo.sub_brokers desks1_ on counterpar0_.broker_code=desks1_.broker_code
left outer join externdb.dbo.broker_routing settlement2_ on counterpar0_.broker_code=settlement2_.broker_code
where desks1_.entity>=0 and settlement2_.entity>=0


select settlement0_.broker_code as broker2_1_, settlement0_.settle_code as settle1_1_,
       settlement0_.settle_code as settle1_0_, settlement0_.broker_code as broker2_2_0_,
       settlement0_.entity as entity2_0_
from externdb.dbo.broker_routing settlement0_
where settlement0_.broker_code in (select counterpar0_.broker_code
                                   from externdb.dbo.brokers counterpar0_
                                   inner join externdb.dbo.sub_brokers desks1_ on counterpar0_.broker_code=desks1_.broker_code
                                   left outer join externdb.dbo.broker_routing settlement2_ on counterpar0_.broker_code=settlement2_.broker_code
                                   where desks1_.entity>=0 and settlement2_.entity>=0)

select desks0_.broker_code as broker1_1_, desks0_.sub_broker_code as sub2_1_, desks0_.broker_code as broker1_0_,
        desks0_.sub_broker_code as sub2_0_, desks0_.entity as entity1_0_
from externdb.dbo.sub_brokers desks0_
where desks0_.broker_code in (select counterpar0_.broker_code
                              from externdb.dbo.brokers counterpar0_
                              inner join externdb.dbo.sub_brokers desks1_ on counterpar0_.broker_code=desks1_.broker_code
                              left outer join externdb.dbo.broker_routing settlement2_ on counterpar0_.broker_code=settlement2_.broker_code
                              where desks1_.entity>=0 and settlement2_.entity>=0)