Eric A. Silva
Blackboard Learn Logo

SQL: List Courses Containing Users With A Specific Email Domain Enrolled

Summary

This query will return a list of courses where users with a specific email domain are enrolled in.

Use Case

Our university uses academic coaches that are contracted by another company. For reporting purposes, we needed to determine which courses contain an academic coach. Since all coaches use their company email address as their username and email, this report searches for all courses containing their domain in the username and email fields.

Code

SELECT DISTINCT cm.course_name
FROM PUBLIC.course_main cm
INNER JOIN PUBLIC.course_users cu
ON cm.pk1 = cu.crsmain_pk1
INNER JOIN PUBLIC.users u
ON u.pk1 = cu.users_pk1
--Filter by Username and Email Address
WHERE (u.user_id LIKE '%<<Insert Email Domain Here>>' OR u.email LIKE '%<<Insert Email Domain Here>>')
--Filter by Course Name
AND cm.course_name LIKE '%Spring2020Module1%'
ORDER BY cm.course_name ASC

Eric Silva

Eric is an Instructional Technologist overseeing Online Student Support Initiatives at The University of Texas Rio Grande Valley and a graduate from Boise State University. Learn more about me.

Add comment

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.