AnsweredAssumed Answered

Open DB - Single user account based on specific course and system roles

Question asked by as0045587 on Jun 29, 2017

Hi guys,

 

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?

 

 

SELECT

  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

FROM

  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;

 

thanks

 

Andy

Outcomes