To set up a lease, I have two tables.
The first one is the contract, it has some relationship fields like id_locador , id_locatario , id_fiador1 , id_fiador2 , among others. These fields are related to a people table: id , nome , endereco , etc.
So all the people involved in the contract come from the same "people" table.
How to make this relationship?
What you need is something like this, for each relation of contrato to your pessoa table, you add left join
select C.ID_CONTRATO, PT.NOME AS TITULAR, PR.NOME AS RESPONSAVEL, PN.NOME AS NOTA
from
CONTRATO C
LEFT JOIN PESSOA PT ON C.ID_PESSOA = PT.ID_PESSOA
LEFT JOIN PESSOA PR ON C.ID_PESSOA_RESPONSAVEL = PR.ID_PESSOA
LEFT JOIN PESSOA PN ON C.ID_PESSOA_NOTA = PN.ID_PESSOA
When you do a left join it will automatically output the results of that join having rows or not, since the left join functions as a join of the tables but meeting its condition as if it were an or if you want to return more concise data use inner join