---

SELECT
    c.id,
    c.number,

    c.entity_id AS contract_entity_id,
    e.short_name AS contract_entity_name,
    e.holding_id AS contract_entity_holding_id,
    h1.id AS direct_holding_id,
    h1.short_name AS direct_holding_name,

    c.affiliate_program_contract_id,
    ce.entity_id AS affiliate_entity_id,
    ea.short_name AS affiliate_entity_name,
    ea.holding_id AS affiliate_entity_holding_id,
    h2.id AS affiliate_holding_id,
    h2.short_name AS affiliate_holding_name

FROM ourpension.contract c

LEFT JOIN ourpension.entity e
    ON e.id = c.entity_id
LEFT JOIN ourpension.holding h1
    ON h1.id = e.holding_id

LEFT JOIN ourpension.contract ce
    ON ce.id = c.affiliate_program_contract_id
LEFT JOIN ourpension.entity ea
    ON ea.id = ce.entity_id
LEFT JOIN ourpension.holding h2
    ON h2.id = ea.holding_id

WHERE c.number = '0025789';