SELECT u.email, if(t1.email is null,0,1) is_t1, if(t2.email is null,0,1) is_t2,
      th.email_from_name as channel,
      lg.primary_location_id, lg.id as group_id,
      -- t1 - sent emails
      t1.facility_name as t1_facility_name,
      t1.fname as t1_fname, t1.lname as t1_lname,
      CONCAT_WS(', ', NULLIF(trim(t1.address),''), NULLIF(trim(t1.address2),'')) as t1_address,
      CONCAT_WS(', ', NULLIF(trim(t1.city),''), NULLIF(trim(t1.state),''), NULLIF(trim(t1.zip),'')) as t1_city_state_zip,
      CONCAT_WS(' ', cm.fname, cm.lname) as cm_name,
      -- t2 - sent letters
      t2.facility_name as t2_facility_name,
      t2.poc as t2_name,
      CONCAT_WS(', ', NULLIF(trim(t2.address),''), NULLIF(trim(t2.address2),'')) as t2_address,
      CONCAT_WS(', ', NULLIF(trim(t2.city),''), NULLIF(trim(t2.state),''), NULLIF(trim(t2.zip),'')) as t2_city_state_zip,
      t2.program_long_name as t2_program_long_name,
      t2.program_short_name as t2_program_short_name,
      t2.website as t2_website,
      t2.website2 as t2_website2,
      t2.support_email as t2_support_email,
      t2.support_phone as t2_support_phone,
      -- regular users - from email_campaign
      if (ec.email is not null,ec.body_html,null) as ec_html
      FROM user u
      LEFT JOIN (select distinct(lower(email)) as email,body_html from email_campaign WHERE campaign_data_id = 14 AND
       sent_date IS NOT NULL AND is_sent_successfully = 1 AND is_unsubscribed = 0) ec ON ec.email = u.email
      LEFT JOIN tmp_anndsc_letter1 t1 ON t1.email = u.email
      LEFT JOIN tmp_anndsc_letter2 t2 ON t2.email = u.email
      LEFT JOIN location loc ON loc.primary_contact_user_id = u.id
      LEFT JOIN location mloc ON mloc.id = u.location_id
      LEFT JOIN location_group lg ON lg.id = COALESCE(loc.location_group_id,mloc.location_group_id)
      LEFT JOIN user cm ON cm.id = lg.manager_id
      LEFT JOIN theme th ON th.id = COALESCE(NULLIF(u.theme_id,0),NULLIF(loc.theme_id,0),mloc.theme_id)
      WHERE LENGTH(u.email)>5 and (ec.email IS NOT NULL OR t1.email IS NOT NULL OR t2.email IS NOT NULL)
      AND u.id NOT IN (select user_id from file where internal_kind_id = 3)
      AND loc.id NOT IN (select location_id from file where internal_kind_id = 3)
      GROUP BY u.email LIMIT 0,10