Cacher is the code snippet organizer for pro developers

SELECT 
a.EmailAddress, 
CASE 
    WHEN fs.Firstname IS NOT NULL THEN fs.Firstname
    WHEN import.Firstname IS NOT NULL THEN import.Firstname
END AS firstname,
CASE 
    WHEN fs.Lastname IS NOT NULL THEN fs.Lastname
    WHEN import.Lastname IS NOT NULL THEN import.Lastname
END AS lastname,
CASE 
    WHEN fs.gender IS NOT NULL THEN fs.gender
    WHEN import.gender IS NOT NULL THEN import.gender
END AS gender

FROM All_record_status_newsletter a
INNER JOIN 
  (SELECT EmailAddress,
    max(Modified) as Modified
  FROM All_record_status_newsletter
  GROUP BY EmailAddress 
) AS b
  ON a.EmailAddress = b.EmailAddress 
  AND a.Modified = b.Modified
  
LEFT JOIN [FS Unique Subscribers] AS fs ON fs.EmailAddress = a.EmailAddress
LEFT JOIN [IMPORT Unique Subscribers] AS import ON import.EmailAddress = a.EmailAddress
LEFT JOIN [Risky_briteverify] AS risky ON risky.EmailAddress = a.EmailAddress
LEFT JOIN [Invalid_briteverify] AS invalid ON invalid.EmailAddress = a.EmailAddress

WHERE a.nboptin = 'True'
AND risky.EmailAddress IS NULL
AND invalid.EmailAddress IS NULL