I need to retrieve a single row per user that can have any course role (from a list - preferably not a student), or certain system roles (from a list). The query below is what I have and, if you run it, you will see that I that I get a large number of multiple rows per user. The users are the important thing, but as a bonus, if anybody can let me know how to only show one of each role (course and system), then that will be amazing.
Can anyone help?
DISTINCT u.user_id,u.student_id, u.firstname, u.lastname, u.email,
LISTAGG(CAST(e.ROLE AS VARCHAR2(50)),', ')
WITHIN GROUP (ORDER BY u.lastname) AS "COURSE ROLES",sr.system_role
bblearn.users u, bblearn.course_users e, bblearn.course_main c, bblearn.course_size s, bblearn.system_roles sr
WHERE u.pk1 = e.users_pk1
AND e.crsmain_pk1 = c.pk1
AND c.PK1 = s.CRSMAIN_PK1
AND (e.role NOT IN ('S') OR sr.system_role IN ('elss'))
AND u.available_ind = 'Y' AND u.row_status = 0 -- enabled and active users
AND c.course_id LIKE('%9999%')
GROUP BY u.user_id,u.student_id,u.firstname,u.lastname,u.email,e.role,sr.system_role
ORDER BY u.user_id;