Before I stuff my head into a blender and hit “puree” I figured I’d bite the bullet and ask for some help.
Every two (or three in this case) years, I have to create an online award nomination app for people to use to nominate people for transportation awards. I try to use the same one (or as much of it as I can) from one nomination period to the next, but things like the physical form (questions, categories, etc) do change, as well as changes to the IT requirements.
Creating the actual form is not so much a problem. The challenging part is creating the portion where admin and judges log in to administer/score the nominations. This year should be easier, because the authentication is being moved out of the app itself to a centralized server. As a result, I’m having to redo significant portions of the previous app.
Anyway, I’m working on the admin view right now, which is a table of nominations, some details about each one, which judges are assigned to them, and the current and average scores (if they exist yet).
The following query gets me pretty much what I want:
SELECT nominees.nom_id
, nominees.tracking_no
, nominees.nominator
, nominees.nominator_email
, nominees.nominator_org
, nominees.nominator_phone
, nominees.nominator_ref
, nominees.nom_title
, nominees.nom_summary
, nominees.nom_org
, nominees.nom_email
, nominees.nom_firstname
, nominees.nom_lastname
, nominees.final
, judges.judge_id
, judges.judge_name
, ROUND(scores.crit1_score * 2.222
+ scores.crit2_score * 2.222
+ scores.crit3_score * 2.222
+ scores.crit4_score * 2.222
+ scores.crit5_score * 2.222
+ scores.crit6_score * 2.222
+ scores.crit7_score * 2.222
+ scores.crit8_score * 2.222
+ scores.crit9_score * 2.222 ) AS total_score
, scores.score_id
, scores.completed
, a.avg_score
FROM ( ( ( ( nominees
LEFT JOIN judgenom
ON judgenom.nom_id = nominees.nom_id
)
LEFT JOIN judges
ON judges.judge_id = judgenom.judge_id
)
LEFT JOIN scores
ON scores.judge_id = judgenom.judge_id
)
LEFT JOIN (
SELECT scores.nom_id
, AVG(scores.crit1_score * 2.222
+ scores.crit2_score * 2.222
+ scores.crit3_score * 2.222
+ scores.crit4_score * 2.222
+ scores.crit5_score * 2.222
+ scores.crit6_score * 2.222
+ scores.crit7_score * 2.222
+ scores.crit8_score * 2.222
+ scores.crit9_score * 2.222
) AS avg_score
FROM scores
GROUP BY scores.nom_id ) AS a
ON a.nom_id = nominees.nom_id
)
ORDER BY a.avg_score DESC, nominees.nom_id, judges.judge_name
The only problem with this query is that the total scores don’t match up to the judges who scored them. I can fix that by adding this:
WHERE scores.nom_id = nominees.nom_id
But then it only shows the nominations that have been scored. Adding the WHERE statement seems to ruin my left join.
I’ve tried adding thisto get the unscored nominations, to no avail:
OR scores.score_id IS NULL
Any ideas?
My table structure:
Nominees: nom_id, plus all the details about the nomination.
Scores: nom_id, judge_id, score_id, plus the scores and comments.
Judgenom: nom_id, judge_id (assigns judges to certain nominations)
Judges: judge_id, judge_name
(I attempted to color code the relationships, hopefully that’s not too annoying!)