Im not that good with it eaither Just use visual editor to play with it This should work but there maybe better ways
SELECT Customer.Id, Customer.Email AS CustomerEmail, [Order].Id AS OrderId, NewsLetterSubscription.Id AS NewsLetterSubscriptionId FROM NewsLetterSubscription INNER JOIN Customer ON NewsLetterSubscription.Email = Customer.Email INNER JOIN [Order] ON Customer.Id = [Order].CustomerId