Returning unselected records

La_Parka

On my site, users respond to a single-question quiz. Each response goes in a table titled "response." While they are going over the question, the site affords the user the option of selecting hints to help them out. Details on each hint are stored in a "hint" table. Every time a hint is selected, a record is entered into a "selectedhint" table. Finally, each hint belongs to a record in the "quiz" table, in addition to corresponding to a category of hints (stored in "hintcategory.") Here is the basic structure of each table:

Hint
+hintID
-hintCatID
-quizID
-hintText

HintCategory
+hintCatID
-hintCatName

Quiz
+quizID
-quizQuestionText

Response
+responseID
-userID
-quizID
-responseText

SelectedHint
-responseID
-hintID

What I am looking to do is display the hintCatNames of every HintCategory that still has unviewed hints in the current response. For example:

The user is taking quiz #1, under responseID #3
There are 3 hints in the hint table that correspond to quizID #1
In the current response, the user has already viewed hints #1 and 3, leaving hint #2 as the only unviewed one.

Hint
hintID  hintCatID      quizID
1             1            1
2             1            1
3             2            1

Response
responseID     quizID
3                  1

SelectedHint
responseID        hintID
3                      1
3                      3

As hint #2 (a member of hint category #1) is the only one that has not been viewed in responseID #3, how would I go about indicating that a hint is available for hintCatID #1?

Fernashes

I'm not sure exactly what you're doing (getting confused) but might it not be easier to add a new column to your hints table, which is called the user's name and then say "used" or "not used" and when they leave, delete it?

La_Parka

Before I started this thread, I was thinking just that, but I decided that might be a little cumbersome and hinder my site's flexibility down the road.

I was thinking more along the lines of a query that left joined SelectedHint to Hint, and inner joined the HintCategory table to Hint. When they were joined, I would then look for null records in the SelectedHint table.

Sxooter

left joins with nulls are often used where one would use a "where id not in (select id from table where not (old where clause stuff goes where)) because they're generally better optimized by most query planners.

Your idea is how I usually do it nowaways as well. If you need help with an exact query, let us know...

La_Parka

Right now, I have a rather complex query:

"SELECT h.hintCatID, hc.hintCatName, COUNT(h.hintCatID) AS hintsLeft
FROM hint h
LEFT JOIN selectedhint sh USING (hintID)
INNER JOIN hintcategory hc ON hc.hintCatID=h.hintCatID
WHERE sh.responseID != ' ".$responseID." '
OR sh.responseID IS NULL
GROUP BY h.hintCatID"

Where $responseID is the ID of the logged-in user's response.

This query screws up whenever a hint is selected as part of another response. When that occurs, hintsLeft increments by one for all other responses, even though actions taken in one response should have no effect on the others.

I realize that this query is way off, but perhaps it will help shed some light on what I am trying to accomplish.

The following text refers to this rudimentary LEFT JOIN:

"SELECT *
FROM hint h
LEFT JOIN selectedhint sh USING (hintID)"

The big problem that I'm having is that left joining selectedhint to hint only gives me NULL responseIDs for hints that haven't been viewed in any response at all.

If I am currently taking part in responseID 36, for example, and hintID #4 was viewed by any of the previous 35 respondents, then hint #4 will not show up as NULL in my result.

If I were to try to filter out records that didn't have a responseID of 36, hintID #4 would be eliminated altogether from the result.

How could I get a quick tally of the hints that haven't been viewed by responseID #36, regardless of whether they have been utilized in other responses or not?