Combine Blackboard 9.1.13 console SQL queries

Question

How do I combine these two SQL Blackboard queries into a single query ("AND") to run on console to list all TESTROLE1 with TESTROLE2 secondary role?

select user_id from users where institution_roles_pk1 = (select pk1 from institution_roles where role_name = 'TESTROLE1');

select user_id from users where pk1 IN (select users_pk1 from user_roles where institution_roles_pk1 = (select pk1 from institution_roles where role_name = 'TESTROLE2'));

score 1 · Answer 1 · answered Aug 19 '14 at 13:23

You do not need to use the user_roles table as the other two tables have all the fields you need for this query to work:

select u.user_id
from users u, institution_roles ir
where u.institution_roles_pk1 = ir.pk1
and ir.role_name = 'TESTROLE1'
and ir.role_name = 'TESTROLE2';

This will give you the list of all users who have both these institutional roles.

Combine Blackboard 9.1.13 console SQL queries

1 Answers1