SELECT REL_ROL4.RELATED_RELATIONSHIP_ID AS RELATIONSHIP_ID ,
       REL_ROL1.RELATED_LEGAL_ENTITY_ID AS provider_id,
       REL_ROL2.RELATED_LEGAL_ENTITY_ID AS legal_entity_of_interest ,
       REL_ROL3.RELATED_LEGAL_ENTITY_ID AS consumer_id
FROM RELATIONSHIP_ROLE REL_ROL4
JOIN RELATIONSHIP_ROLE REL_ROL1
ON REL_ROL4.RELATED_RELATIONSHIP_ID = REL_ROL1.RELATIONSHIP_ID
JOIN RELATIONSHIP_ROLE REL_ROL2
ON REL_ROL1.RELATIONSHIP_ID = REL_ROL2.RELATIONSHIP_ID
JOIN RELATIONSHIP_ROLE REL_ROL3
ON REL_ROL4.RELATIONSHIP_ID = REL_ROL3.RELATIONSHIP_ID
JOIN provider prov
ON REL_ROL1.RELATED_LEGAL_ENTITY_ID = prov.LEGAL_ENTITY_ID
JOIN consumer cons
ON REL_ROL3.RELATED_LEGAL_ENTITY_ID = cons.LEGAL_ENTITY_ID
WHERE REL_ROL3.RELATED_LEGAL_ENTITY_ID =1251
AND REL_ROL2.RELATED_LEGAL_ENTITY_ID  != REL_ROL1.RELATED_LEGAL_ENTITY_ID;

Please help.