I’m working with a table called LearnerRecords that contains information about which courses each student has finished. I need to create a query that finds the courses each student still needs to complete. The table holds three columns: LID, FullName, and CompletedCourse. For example, my data is structured as follows:
LearnerRecords
LID FullName CompletedCourse
10 Sam Math101
10 Sam Sci102
10 Sam Hist103
20 Alex Math101
20 Alex Lang104
20 Alex Sci102
30 Pat Sci102
30 Pat Lang104
30 Pat Art105
The result I am looking for should show the courses not taken by each student. An expected output might look like:
LID FullName IncompleteCourse
10 Sam Lang104
10 Sam Art105
20 Alex Hist103
20 Alex Art105
30 Pat Math101
30 Pat Hist103
hey, i ended up doing a cross join between a table of all courses and learner records then used a left join to flag missing courses. it worked decently well; try adjusting it as needed for your data.
Based on my experience, a working approach uses a NOT EXISTS clause to filter out courses already completed by each student. Essentially, you create a separate table or list of all courses and then for every student, you check which courses are not present in the LearnerRecords. This eliminates the need for extensive cross joins and left joins. I’ve also found that this method is more readable and easier to maintain over time. Adjusting indexes and ensuring proper execution plans can further improve performance when dealing with larger datasets.
Hey Nate_45Guitar, I’ve been playing around with something similar and found that using a CTE to generate a full course list really cleans up the process. What I did was create a common table expression listing all courses in one place and then joined that with the LearnerRecords to filter out the ones each student already finished. I think it makes the logic much clearer and easier to debug if something goes awry
For instance, you’ll have something like:
WITH AllCourses AS (
SELECT ‘Math101’ AS Course UNION ALL
SELECT ‘Sci102’ UNION ALL
SELECT ‘Hist103’ UNION ALL
SELECT ‘Lang104’ UNION ALL
SELECT ‘Art105’
)
…then you could join that with your student IDs and filter using a NOT IN or NOT EXISTS subquery.
I’m curious though—how do you plan to store your master course list? Is it a fixed set, or do they change often? I sometimes wonder if maintaining a separate courses table might be beneficial in the long run. What do you think?
hey nate, i trued a correlated subquery approach which directly filters out courses not in the completed list. works well for small course sets though might slow on huge ones. hope this gives u a fresh angle, good luck!
Based on my experience, another viable approach uses the EXCEPT operator to directly compare the master course list with the courses each student has completed. You could create a CTE containing all courses and then use a query that selects the courses a student should complete, EXCEPT those already completed. This method is straightforward and transparent because it clearly separates the complete list from the student records. I found that it simplifies debugging and is adaptable if the course list is updated over time by managing it in its own table.
