 SELECT d.id, u.id AS userid, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname, u.lastname, b.name AS badgename,
                                      t.criteriatype, t.method, d.dateissued,
                                      (SELECT c.shortname FROM {course} c WHERE c.id = b.courseid) as course
                                 FROM {badge_issued}   d
                                 JOIN {badge}          b ON d.badgeid = b.id
                                 JOIN {user}           u ON d.userid  = u.id
                                 JOIN {badge_criteria} t ON b.id      = t.badgeid
                                WHERE t.criteriatype != 0
                              ORDER BY u.username