Finding classes with only module 5 in SQL

Courses
1

Hey everyone! I’m scratching my head over this SQL problem. I’ve got a table with classes and their modules. I want to find classes that only have module 5. Here’s what my data looks like:

ClassID | Class | Module
   1    |   A   |   5
   2    |   B   |   5
   3    |   B   |   11
   4    |   C   |   5
   5    |   C   |   3
   6    |   D   |   5

I tried some stuff with two separate queries and comparing files, but it feels messy. Is there a way to do this in just one SQL query? I’d love to get only classes A and D as the result.

Any tips would be awesome! Thanks a bunch!

2

Hey Liam39! That’s a tricky one, but I think I might have a solution for you. :thinking:

Have you tried using a GROUP BY clause with a HAVING condition? Something like this might work:

SELECT Class
FROM YourTableName
GROUP BY Class
HAVING COUNT(*) = 1 AND MAX(Module) = 5

This groups all the classes together, then only selects the ones that have exactly one row (COUNT(*) = 1) and where that row’s Module is 5 (MAX(Module) = 5).

What do you think? Does that give you the result you’re looking for?

I’m curious, though - what kind of project are you working on that needs this kind of query? Sounds interesting!

3

yo Liam, try this:

SELECT Class
FROM ur_table
GROUP BY Class
HAVING SUM(CASE WHEN Module = 5 THEN 1 ELSE 0 END) = COUNT(*)

this counts all rows for each class and compares it to rows with Module 5. if they match, it’s what ur after. lemme know if it works!

4

I’ve encountered a similar issue before, and here’s an approach that worked for me:

SELECT Class
FROM YourTable
GROUP BY Class
HAVING MIN(Module) = 5 AND MAX(Module) = 5

This query groups the classes and checks if both the minimum and maximum Module values are 5. It effectively filters out classes with any modules other than 5.

One advantage of this method is its efficiency, especially with larger datasets. It avoids using CASE statements or subqueries, which can impact performance.

Remember to replace ‘YourTable’ with your actual table name. If you’re still having trouble, double-check your table structure and data types. Sometimes, unexpected results can occur due to data inconsistencies or type mismatches.