I need a second set of eyes on this query

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!)

indeed, it usually does

so add it to the ON clause instead :slight_smile:

I cannot! Access won’t allow it.

The sad part is, I plan to eventually port this to MSSQL 2008, but we don’t have a development environment set up for that yet. :frowning:

try parentheses around the ON conditions

LEFT  
  JOIN scores
    ON ( 
       scores.judge_id = judgenom.judge_id
   AND scores.nom_id = nominees.nom_id
       )

Yeah, I’ve tried that and several other variations on the paren thing, and I just get this:

Syntax error in JOIN operation. 

I got a little traction by changing this:


    	, 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

to this:


    	, ( SELECT 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 ) 
            FROM scores AS b
            WHERE b.judge_id = judgenom.judge_id
            AND b.nom_id = judgenom.nom_id
            ) AS total_score

But for some reason my score IDs don’t match up to the right nominations. :badpc:

Whoops! :slight_smile:

That subquery should have been:


    	, ( SELECT ROUND(scores.crit1_score * 2.222
			+ b.crit2_score * 2.222
          		+ b.crit3_score * 2.222
        		+ b.crit4_score * 2.222
       		 	+ b.crit5_score * 2.222
       			+ b.crit6_score * 2.222
        		+ b.crit7_score * 2.222
        		+ b.crit8_score * 2.222
        		+ b.crit9_score * 2.222 )
            FROM scores AS b
            WHERE b.judge_id = judgenom.judge_id
            AND b.nom_id = judgenom.nom_id
            ) AS total_score

Another problem I found was that I think my data had a problem. Only certain judges are assigned certain nominations (via the judgenom table) but I think my scores included judge/nomination combos that shouldn’t have been allowed.

I’ve cleared the scores table and am putting in new test entries.

i feel you, man :slight_smile:

I have sort of solved this by stuffing a little query in the output loop that grabs the score ID based on the judge ID and nomnation ID. It’s UGLY but it works until I can get this thing in a proper database. Given that there will only be three admin checking this app a few times a week, I don’t think it’s going to be the end of the world if I have to stick with it.

I have almost everything done, except for the judges table. Judges view this page to score the nominations assigned to them, but for some reason it’s showing a score from another judge.


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 AS fullname
        , nominees.nom_category
        , scores.crit1_score
	, scores.crit2_score
	, scores.crit3_score
	, scores.crit4_score
	, scores.crit5_score
	, scores.crit6_score
	, scores.crit7_score
	, scores.crit8_score
	, scores.crit9_score
        , scores.completed
        , scores.score_id
        , judgenom.judge_id
FROM ( nominees
INNER JOIN judgenom
	ON judgenom.nom_id = nominees.nom_id
        )
LEFT JOIN scores
	ON scores.nom_id = nominees.nom_id
WHERE judgenom.judge_id = <cfqueryparam value="#session.userid#" cfsqltype="cf_sql_numeric">
ORDER BY nominees.nom_title

It shows the proper nominations, but it shows one as being scored when that score actually belongs to another judge. For some reason the judge ID (the session.userid variable) is being ignored for the scores. I need a second condition on joining the scores table, but OF COURSE Access won’t let me do that. :headbang: :headbang:

I’ve finally given up on this and put in a request to have this database imported into MSSQL Server. Since the database has no data yet, it’ll be a good one for the server people to practice importing.

he said, with no trace of sarcasm :wink:

haha well you might remember what happened the last time they imported one (well more than one) of my databases. (Primary keys? What primary keys?)