SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code SQL:
    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:

    Code SQL:
    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:

    Code SQL:
    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!)
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I can fix that by adding this:

    Code SQL:
    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.
    indeed, it usually does

    so add it to the ON clause instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    so add it to the ON clause instead
    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.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I cannot! Access won't allow it.
    try parentheses around the ON conditions
    Code:
    LEFT  
      JOIN scores
        ON ( 
           scores.judge_id = judgenom.judge_id
       AND scores.nom_id = nominees.nom_id
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try parentheses around the ON conditions
    Code:
    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:

    Code:
    Syntax error in JOIN operation.
    I got a little traction by changing this:

    Code SQL:
        	, 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:

    Code SQL:
        	, ( 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.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Whoops!

    That subquery should have been:

    Code SQL:
        	, ( 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.
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Another problem I found was that I think my data had a problem.
    i feel you, man
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code SQL:
    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.
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    <cfset myblog = "http://cydewaze.org/">

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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?)
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •