12/03/2014

SQL query: courses with iClicker registrations


SELECT cm.course_id, u.firstname, u.lastname, u.email
  FROM bb_bb60.course_main cm
  INNER JOIN bb_bb60.course_users cu ON cm.pk1 = cu.crsmain_pk1
  INNER JOIN bb_bb60.users u ON u.pk1 = cu.users_pk1
  WHERE cm.row_status = 0 AND cu.row_status = 0 AND cu.available_ind = 'Y' AND cu.role = 'P' AND cm.course_id LIKE '%20148' AND cm.pk1 IN (
    SELECT TO_NUMBER(regexp_replace(ir.course_id, '_(.*)_1', '\1'))
    FROM bb_bb60.iclicker_registration ir WHERE regexp_like(ir.course_id,'_[0-9]+_1')
  );