• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

SQL Question

Supermercado

Supermercado

Diamond Member
This is probably going to be quite lengthy and if you make it through, I applaud your patience. I?m working on a project using ASP.NET and SQL Server and I know just enough SQL to be dangerous, I think. I?ve been trying to come up with a query to do what is described below and what I?ve got is really really close but it?s not quite there and I?m having a hard time figuring out what I?m missing.

What I?m trying to do is build a custom quiz application. The end goal is for a user to come on, start taking a quiz, and then be graded and have the ability to retake it, taking only the questions they missed on the previous attempt(s). The database I?ve got designed handles all this just fine, I think, and is as described below. There are multiple levels and sections and subsections to this but all that?s really applicable is that a quiz is part of a subsection. I?ve only included (what I think are) the relevant fields in the tables.

Questions
ID
SubSectionID
CorrectAnswerID
SortNum

Attempts
ID
UserID
SubSectionID

AttemptsSub
ID
AttemptID
QuestionID
SelectedChoiceID

Users
ID

All attempts (time, date, user, and so forth) have to be recorded, as well as what answers were picked on what attempt. The idea is that a record of an attempt is kept in the Attempts table and the specifics of that attempt are in the AttemptsSub table with a foreign key back to the Attempts table. In the mix is also an Answers table but I don?t think it?s really applicable to the problem at hand.

What I?m trying to do is write a query that will return me the question ID and correct answer ID of the next question in the sequence for the user, so long as there is a next question.

The query I?ve got to this point is below and it?s very close but I?m getting some errors in what I?m getting back. I?m working on trying to debug what?s being returned but so far, it?s not making a whole lot of sense.

SELECT q.ID, q.CorrectAnswerID
FROM Questions AS q
INNER JOIN Attempts AS a ON q.SubSectionID = a.SubSectionID
INNER JOIN Users AS u ON u.ID = a.UserID
INNER JOIN AttemptsSub AS s ON s.AttemptID = a.ID
AND q.CorrectAnswerID <> s.SelectedChoiceID
WHERE (q.SubSectionID = @SubSectionID) AND (a.UserID = @UID)
ORDER BY SortNum ASC

@SubSectionID and @UID are parameters.

I hope that there?s enough information there for someone to give me a push in the right direction. It feels like it?s so close but just not quite there. Any help would be very much appreciated.

Edit: Just fixing a typo in the query here that's correct in the actual query.

Update:
I've narrowed down what's going on with what I'm getting back. I was playing with my test data and it looks like there's an increase in data each time I add a new bit of test data.

My test data is 5 questions, numbered 1-5. The first time I run the query with just q1 answered correctly, I get the expected result, which is why I thought the query was working. When I add a second bit of data to answer q2 correctly, I get 8 results instead of the expected 3. It's returning the correctly answered questions (1, 2) once and the incorrectly answered questions twice (3, 4, 5). If I do the same thing again and answer q3 correctly, I get 12 results, the correct questions (1, 2, 3) twice each and the incorrect questions (4, 5) three times each. Same again with q4, 16 rows, three times and four times.

What might cause something like that? I've never seen anything like this.
 
I've never even heard of MINUS before. I think I looked briefly at subqueries earlier, but I can look again and see if anything strikes me.

Does that mean that that's the way to do it or if I'm having to resort to that, that there's something incorrect with the query? Or is there no way to generalize that?

Thanks for the reply.
 
I didn't look careful, but I think "AND q.CorrectAnswerID <> s.SelectedChoiceID" is what causing the problem for you.

Let said you know all the correct choices for the test and the correct choices for a given user.

correct choices for user
minus
correct choice for test

will let you know which question(s) a given user need to take. if all the answers are the same, it will return nothing.
 
Hmmm, that's an idea. I'll look at the minus stuff and see if that gets me anywhere. That's basically what I was trying to do with the above statement but maybe I got confused along the way.
 
I think it's because you have that attemptsSUB table. I would move the UserID field into the attempssub table and call it Attempts, and change your query to the folllowing:

SELECT q.ID, q.CorrectAnswerID
FROM Questions q
INNER JOIN Attempts s ON s.QuestionID = q.ID AND q.CorrectAnswerID = s.SelectedChoiceID
INNER JOIN Users u ON u.ID = s.UserID
WHERE (s.UserID = @UID)
ORDER BY SortNum ASC

EDIT: How do you ID the quiz that a user took? say if he took it more than once.
 
I thought about doing that. The only problem with that is that there is some data in the current Attempts table that really belongs to an attempt rather than a subattempt. I left it out of the OP because it's kind of irrelevant to the problem I'm getting but in the current table, it's got things like the start time, end time, and so on that are really related to the whole attempt as opposed to the answering of a specific question. Also, if everything's in one table, I have no way to determine whether a question was answered as part of the first attempt or third attempt on a given quiz.
 
I just thought about something else that I think may raise the scope and challenge of the problem. Even if I'm able to get the above query working, without tracking this somewhere else in the database, it's going to repeatedly return the same question if it's answered incorrectly, which is not the behavior I want. I may have to rethink this entirely now.
 
Yeah. I'm not sure if I'm good enough with SQL to do the necessary stuff, though. I could do it all programmatically out in the C# but then I lose the benefit of the stored procedure and I'd like to have that and the nice, neat query that I can just call and get back the next question over and over. This is going to take some thinking, I think 🙂

Edit: The quiz history table basically is what the AttemptsSub table is; what did you mean by the quiz header table?
 
Just for the sake of completeness, I decided to just do what I'm after in code and wrote that up the other day. There ended up being far more than I was going to be able to do in just SQL in any sort of reasonable time so I just went at it in code.
 
Use a subquery.

Come up with a query that will return all the questions attempted by a user. Something like:
SELECT QuestionID FROM Attempts, AttemptsSub WHERE Attempts.ID = AttemptsSub.AttemptID AND Attempts.UserID = @User_Id

Next stage is to select Questions that aren't in that list. This is done with a subquery:
SELECT Questions.ID FROM Questions WHERE Questions.ID NOT IN
(SELECT QuestionID FROM Attempts, AttemptsSub WHERE Attempts.ID = AttemptsSub.AttemptID AND Attempts.UserID = @User_Id).

You will probably need a limit and order clause. The result should be something like this:
SELECT TOP 1 Questions.ID FROM Questions WHERE Questions.ID NOT IN
(SELECT QuestionID FROM Attempts, AttemptsSub WHERE Attempts.ID = AttemptsSub.AttemptID AND Attempts.UserID = @User_Id)
ORDER BY SortNum ASC


 
The problem is that your join to the AttemptsSub table is not sufficient. You are only joining using the SubID, which is going to link multiplicatively for every question in the subset. That inner join should be:
INNER JOIN AttemptsSub AS s ON s.AttemptID = a.ID AND s.QuestionID = q.ID
 
Well, that certainly makes sense.

I've already completed this part of the project using a couple of separate queries and code, which may have been the thing to do, anyway, due to some other issues, but if I've got time, I'll definitely go back and try out the adjusted query and see if it would have produced the results I wanted and then kick myself 🙂

Thanks!
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top