SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  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'm stuck on this query with multiple joins.

    The database is MS Access.

    I have a page for an online nomination system that allows nominations to be entered online, judges to score those nominations, and admins to view the nominations and the scores. The problem is with the admin page that shows a summary of what's been entered.

    I have tables for:

    users (these are people submitting nominations, the judges, and the admin)
    nominees (these are the nominations, with a column for nomination ID and a column for the ID of who submitted the nom)
    judgenom (column for user (judge) IDs and the nomination IDs to which they've been assigned)
    scores (the scores for each nomination, including columns for the nomination ID and the ID of the user (judge)

    My query looks like this:

    Code:
    SELECT nominees.nom_id
    		, nominees.nom_title
    		, nominees.tracking_no
    		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
    		, nominees.finalized
    		, judges.user_id AS judge_id
    		, judges.user_name AS judge
    		, users.user_name AS nominator
    		, users.user_org
    FROM ( ( ( 
           nominees
    INNER JOIN users
    		ON users.user_id = nominees.submit_id
    		)
    LEFT OUTER JOIN judgenom
    		ON judgenom.nomination_id = nominees.nom_id
    		)
    LEFT OUTER JOIN users AS judges
    		ON judges.user_id = judgenom.judge_id
    		)
    And that works perfectly. The problem is when I try to join the scores table, using this:

    Code:
    LEFT OUTER JOIN scores
    		ON scores.nomination_id = nominees.nom_id
    As soon as one judge enters a score, the other judge assigned to that nomination vanishes from the table. And I can't for the life of me figure out why.
    <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,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you were to provide us with some comprehensive sample data, i'm sure we can figure it out

    (by comprehensive, i don't mean just sample rows for each table, but rather, data that illustrates the multiple judges and multiple scores for a nomination, with correct key relationships between related rows)
    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)
    You mean like a URL? Without making an account for people to log in (which would be frowned upon by my office) the best I could probably do is a screenshot.
    <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,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can you not write up some CREATE TABLE and INSERT statements?

    i realize that this is a lot harder to do in msaccess (in mysql, you can generate them both with one command, mysqldump)

    but unless i can see which judges are related to which scores and which nominations, i don't think i can debug your query
    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)
    The closest thing I can find in Access gives me a 38 page text dump.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not sure what else to suggest

    i can't help you if i can't see your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What version of MS Access are you using?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Please also post the query (the exact query, not a look-alike) that is giving you the problem.

  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)
    Ok, here's my original query (hurray for backups!):

    Code:
    SELECT nominees.nom_id
    		, nominees.nom_title
    		, nominees.tracking_no
    		, nominees.nom_summary
    		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
    		, nominees.finalized
    		, judges.user_id AS judge_id
    		, judges.user_name AS judge
    		, users.user_name AS nominator
    		, users.user_org
    		, scores.cpi_score 
    		, scores.inno_score 
    		, scores.pc_score 
    		, scores.int_score
    		, scores.equ_score
    		, scores.ls_score
    		, scores.drer_score
    		, scores.iis_score
    		, scores.css_score
    FROM ( ( ( (
           nominees
    INNER JOIN users
    		ON users.user_id = nominees.submit_id
    		)
    LEFT OUTER JOIN judgenom
    		ON judgenom.nomination_id = nominees.nom_id
    		)
    LEFT OUTER JOIN users AS judges
    		ON judges.user_id = judgenom.judge_id
    		)
    LEFT OUTER JOIN scores
    		ON scores.nomination_id = nominees.nom_id
    		)
    WHERE scores.judge_id = judgenom.judge_id
    OR scores.judge_id IS NULL
    ORDER BY nominees.nom_id, judgenom.judge_id
    And here's what the db looks like (the ... means that there are more columns,. but they're not involved in the joins or where clauses):

    [nominees]
    nom_id (primary key, auto number)
    submit_id (user ID of person submitting the nomination)
    nom_title
    nom_summary
    ...

    [users]
    user_id (primary key, auto number)
    user_name
    user_email
    ...

    [judgenom] (assigns nominations to judges)
    jn_id (primary key, auto number)
    judge_id (user ID of judge)
    nomination_id (nom_id of nomination)

    [scores]
    score_id (primary key, auto number)
    nomination_id (nom_id of nomination that this score is for)
    judge_id (user ID of judge who submitted this score)
    ...

    My original problem is that when viewing the summary page (that's generated from the query above), you can see all the judges assigned to each nomination. But as soon as one of those judges submits a score, the other two judges vanish. It's almost like I have the wrong type of join, but Access seems very limited to what I can do (i.e. no full outer joins).

    Let me know if I need to provide any more info.
    <cfset myblog = "http://cydewaze.org/">

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try:
    Code:
    SELECT nominees.nom_id
    		, nominees.nom_title
    		, nominees.tracking_no
    		, nominees.nom_summary
    		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
    		, nominees.finalized
    		, judges.user_id AS judge_id
    		, judges.user_name AS judge
    		, users.user_name AS nominator
    		, users.user_org
    		, scores.cpi_score 
    		, scores.inno_score 
    		, scores.pc_score 
    		, scores.int_score
    		, scores.equ_score
    		, scores.ls_score
    		, scores.drer_score
    		, scores.iis_score
    		, scores.css_score
    FROM ( ( ( (
           nominees
    INNER JOIN users
    		ON users.user_id = nominees.submit_id
    		)
    LEFT OUTER JOIN judgenom
    		ON judgenom.nomination_id = nominees.nom_id
    		)
    LEFT OUTER JOIN users AS judges
    		ON judges.user_id = judgenom.judge_id
    		)
    LEFT OUTER JOIN scores
    		ON scores.nomination_id = nominees.nom_id
                    AND scores.judge_id = judgenom.judge_id
    		)
    ORDER BY nominees.nom_id, judgenom.judge_id

  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)
    Guido, that was actually one of the first things I tried, but it results in this:

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Join expression not supported.

    So I assumed it was an Access limitation.
    <cfset myblog = "http://cydewaze.org/">

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I guess that has to do with the psychopathic way Access uses those brackets. You'll probably have to do something really crazy like
    Code:
    LEFT OUTER JOIN scores
    		ON ((scores.nomination_id = nominees.nom_id)
                    AND (scores.judge_id = judgenom.judge_id))
    		)
    Last edited by guido2004; Jan 18, 2012 at 10:21. Reason: typo

  13. #13
    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)
    UGH! I hate Access. I've tried several paren variations, but I feel like I'm trying to guess a strong password or something. Why did they make it like this?
    <cfset myblog = "http://cydewaze.org/">

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Why did they make it like this?
    psychopathic

    try saving the query, then running it as a saved query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    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 saving the query, then running it as a saved query
    I'm not completely sure how to do this.
    <cfset myblog = "http://cydewaze.org/">

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    presumably you know how to save a query, right?

    start by saving it, then open it in design view, and check the diagram to make sure it's joining on both columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    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 actually never used the Access WYSIWYG for anything more than setting up the database or copy/pasting data in datasheet mode. My queries have always been done in the web apps.
    <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
  •