Complex SQL Query with Business Logic

Question

I'm stuck with a complex query where I need to implement some business logic. Below are the tables that I'm having (I have omitted extra columns for readabality)

Team - TeamID, TeamName
TeamMember - MemberID, TeamID, MemberName
Question - QuestionID, CategoryID, DifficultyLevelID, Question
Category - CategoryID, CategoryName
DifficultyLevel - DifficultyLevelID, DifficultyLevelName
QuestionOffered - QuestionOfferedID, QuestionID, OfferedTo

Now, The business logic is, I have 10 categories (like Science, History, Geography etc.) and 3 Difficulty level (i.e. Easy, Medium, Hard). Each category and difficulty level have 5 questions.

So 3 x 5 = 15 questions for each category (5 Easy, 5 Medium, 5 Hard). Total 15 x 10 (categories) = 150 questions.

Now I want that everytime a user comes in, I'll check whether I have already offered (can be tracked from QuestionsOffered table) all the 5 questions under that category and difficulty level, if so, then I won't show that category and difficulty level again.

So, say I come in first time I'll get all the category and then selecting a category I'll get all the difficulty level. Now I select Easy and proceed. So under each category I can select a particular difficulty level 5 times after that I'll get the category and other difficulty levels (i.e. Medium and Hard) after I have done with all 5 easy.

So, in this way, if I'm done with all 5 easy, 5 medium and 5 hard questions under a same category, the category itself won't show up.

Ofcourse I can implement this logic in programming for populating the category but that would end up with numerous lines of code.

So is there any way to check this business logic in SQL? So that I will get the data to populate?

Answer 1

inner join your questions, categories, and levels together, then left join to the questions already offered. Filter where any field in the questions offered table is null, and you will have a list of unanswered questions? perhaps?