On my site, users take a test, during which they have the option of selecting hints from a number of categories. When they've finished the qualitative portion of the exam, they're asked to rank the importance (from 1 to 3, for example, if 3 categories were selected) that each hint category played in their decision-making process. They are only asked to rank the hint categories that they actually chose during the test, and the feedback is recorded as such in the database:
feedback
responseID int(10)
hintCategoryID int(10)
importance int(10)
I'm looking to create a report that will display how each respondent (distinguished by responseID) prioritized their chosen hint categories. For any categories that they didn't choose, a "NULL" value will be displayed.
What I'm unsure about is how to return NULL values for categories not selected by a particular responseID. I've tried...
"SELECT *
FROM feedback f
RIGHT JOIN hintcategory hc ON (f.hintCategoryID = hc.hintCategoryID)"
...but it doesn't recognize a hintCategoryID as NULL for one responseID if it was selected by another responseID.
If such a report is feasible, is a join the right way to go about generating it? When running a query on all responses, is it possible to look for NULL hintCategoryIDs for each individual responseID?
