How to use two Join followed SQL

Question

How can I use two joins in a row, and the second will use the table data generated by the first? I want it to relate the Join by the same data, where in the first Join it unites the data of table A with those of table B only when RG and CPF is equal, so I want to change the other values that do not "match" between the tables to the string "ANONIMO", only then to perform another Join that would result in all the data of table A appearing (in different "house do_friend" and counting how many times each of the houses appear). This code below is not working completely, because there is data that is not appearing.

     SELECT amigo, casa do_amigo, 
  COUNT (casa do_amigo) as números_de_casas_do_amigo
FROM
  (
  SELECT amigo, casa do_amigo, 
    IF (part3 = Classificacao, Classificacao, "ANONIMO") as parte3,
    IF (part4 = Sub_Classificacao, Sub_Classificacao, "ANONIMO") as parte4
    FROM 
      (
      SELECT *
      FROM 
        ( 
          SELECT amigo, tipo_de_residencia, bairro do_amigo,  endereço do_amigo,
          SPLIT (endereço do_amigo, '\\') [SAFE_ORDINAL (1)] as part1,
          SPLIT (endereço do_amigo, '\\') [SAFE_ORDINAL (2)] as part2,
          SPLIT (endereço do_amigo, '\\') [SAFE_ORDINAL (3)] as part3,
          SPLIT (endereço do_amigo, '\\') [SAFE_ORDINAL (4)] as part4,
          SPLIT (endereço do_amigo, '\\') [SAFE_ORDINAL (5)] as part5,
          SPLIT (endereço do_amigo, '\\') [SAFE_ORDINAL (6)] as part6
          FROM `codes.enderecoamigo.tabela1`
          WHERE tipo_de_residencia = "CASA" AND amigo = "roger"
        ) AS A
        LEFT JOIN 
        (
          SELECT *
          FROM `codes.enderecoamigo.tabela2` 
        ) AS B
        ON A.part3 = B.rg  AND A.part4 = B.cpf)) AS C
JOIN
        (
          SELECT RG as Reg_geral, cpf as Cad_PF, casa_do_amigo as moradia
          FROM `codes.enderecoamigo.tabela2` 
        ) AS D
ON C.parte3 = D.Reg_geral  AND C.parte4 = D.Cad_PF
GROUP by amigo, casa_do_amigo