This is currently setup for failed job notification.  change the first line to any of the other statuses if needed.  replace the xxxxxxxxxxx with the users email:

select from jobs where jobs.fail_notification_list_id in (

  select from notification_lists nl where in (

    select nlu.notification_list_id from notification_list_users nlu where nlu.notify_contact_type='User' and nlu.notify_contact_id in (

      select from users where users.email_address like xxxxxxxxxxxxxx%')))