gistfile1.txt

select recruits.first_name as first_name , recruits.last_name as last_name, recruits.email as email,
          home_address.street as home_address_street,
          home_address.street2 as home_address_street2,
          home_address.zip as home_address_zip,
          home_address.city as home_address_city,
          home_address.state as home_address_state,
          home_address.country as home_address_country,
          home_address.phone as home_address_phone,
          home_address.fax as home_address_fax,
          work_address.street as work_address_street,
          work_address.street2,work_address.street2,
          work_address.zip as work_address_zip,
          work_address.city as work_address_city,
          work_address.state as work_address_state,
          work_address.country as work_address_country,
          work_address.phone as work_address_phone,
          work_address.fax as work_address_fax
          FROM recruits 
          JOIN conference_recruits on conference_recruits.recruit_id = recruits.id 
          LEFT JOIN recruit_addresses as work_address on work_address.recruit_id = recruits.id and
          work_address.is_work_address = true and work_address.is_mailing_address = true
          LEFT JOIN recruit_addresses as home_address on home_address.recruit_id = recruits.id and
          home_address.is_home_address = true and home_address.is_mailing_address = true
          WHERE
          coalesce(home_address.street,work_address.street) is not null and
          coalesce(home_address.zip,work_address.zip) is not null and
          coalesce(home_address.state,work_address.state) is not null and
          (conference_recruits.commit = 'Yes' or conference_recruits.commit = 'Maybe') and not exists
          (select users.id from users join conference_users on conference_users.user_id = users.id 
          and conference_users.conference_id = #{::Conference.working_conference.id}
          where conference_users.is_offline_reviewer = true and 
          ( users.id = recruits.user_id or 
          (rtrim(ltrim(upper(recruits.first_name))) = rtrim(ltrim(upper(users.first_name))) and
          rtrim(ltrim(upper(recruits.last_name))) = rtrim(ltrim(upper(users.last_name))))))