SELECT surname,firstname,attr.*
FROM borrowers
LEFT JOIN (
  SELECT borrowernumber,
  MAX(CASE WHEN code = 'PROFESSION' THEN attribute ELSE NULL END) AS PROFESSION,
  --autres attributs
  FROM borrower_attributes
  GROUP BY borrowernumber
) AS attr USING (borrowernumber);