select a.email
      ,a.firstName
  from tblInTouchMailshot as a
       left outer join tblInTouchMailshotOptOut as b
                    on b.id=a.id
                   and b.email=a.email
       left outer join tblInTouchMailshotSent as c
                    on c.id=a.id
                   and c.email=a.email
                   and c.newsID=223
       left outer join tblCustomer as d
                    on d.id=a.customerID
                   and d.email=a.email
                   and CountryID=1
 
 where a.active=1
   and b.email is null
   and c.email is null
   and d.email is null
 group by a.email
         ,a.firstName

select email
      ,firstName
  from tblInTouchMailshot as a
 where active=1
   and not exists (select 1
                     from tblInTouchMailshotOptOut as b
                    where b.id=a.id
                      and b.email=a.email
                  )
   and not exists (select 1
                     from tblInTouchMailshotSent as c
                    where c.id=a.id
                      and c.email=a.email
                      and c.newsID=223
                  )
   and not exists (select 1
                     from tblCustomer as d
                    where d.id=a.customerID
                      and d.email=a.email
                      and d.countryID=1
                  )