Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Location
    Brisbane, Australia
    Posts
    3

    Unanswered: SubQuery not functioning the way it's meant to

    TAFE created database for assessment (based on athletics carnival results) ...

    I have to provide the following information:
    What schools either gained first place or hold a record in an 800m event? List the school names in alphabetically order.

    The relationships are as follows (colours represent common entities):
    tblAthletes = (Ath_ID, Ath_Name, Ath_Sex, School_Code, YOB, Current)
    tblEvents = (Ev_No, Ev_Name, Ev_Sex, Age_Div, Rec_ID, Rec_Min, Rec_Sec, Rec_Year)
    tblResults = (Ev_No, Place, Ath_ID, Time_Min, Time_Sec)
    tblPoints = (Place, Points)
    tblSchools = (School_Code, School_name)

    The Microsoft Access SQL Code that I have is as follows:
    Code:
    SELECT DISTINCT tblSchools.School_Name
    FROM (((tblAthletes INNER JOIN tblResults ON 
        tblAthletes.Ath_ID = tblResults.Ath_ID) INNER JOIN tblPoints 
        ON tblResults.Place = tblPoints.Place) INNER JOIN tblSchools 
        ON tblAthletes.School_Code = tblSchools.School_Code) INNER 
        JOIN tblEvents ON tblResults.Ev_No = tblEvents.Ev_No
    WHERE tblAthletes.Ath_ID IN (
      SELECT tblEvents.Rec_ID
      FROM tblEvents, tblAthletes, tblSchools
      WHERE Rec_ID = Ath_ID
      AND tblAthletes.School_Code = tblSchools.School_Code
      AND Ev_Name="800m")
    OR tblAthletes.Ath_ID IN (
      SELECT Ath_ID
      FROM tblResults
      WHERE Place="1");
    My problem is this:
    If I run the first sub query seperately as:
    Code:
    SELECT tblEvents.Rec_ID, tblAthletes.Ath_Name, tblSchools.School_Name
    FROM tblEvents, tblAthletes, tblSchools
    WHERE Rec_ID = Ath_ID
    AND tblAthletes.School_Code = tblSchools.School_Code
    AND Ev_Name="800m";
    I get an extra value than the full code.

    My results of the full query is as follows:
    School name
    Burnside State High School
    Coolum State High School
    Nambour Christian College
    Nambour State High School
    Noosa District State High School
    Pomona Secondary School
    St John's College
    Sunshine Beach State High School

    However, I should have "Maroochydore State High School" in there as well.
    I have absolutely no idea what I'm doing wrong. I've gone through all my class notes, and can't find anything. I'm running out of ideas...

    If anyone could help, that would be totally awesome.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My problem is this:
    If I run the first sub query seperately as:
    Code:

    SELECT tblEvents.Rec_ID, tblAthletes.Ath_Name, tblSchools.School_Name FROM tblEvents, tblAthletes, tblSchools WHERE Rec_ID = Ath_ID AND tblAthletes.School_Code = tblSchools.School_Code AND Ev_Name="800m";

    I get an extra value than the full code.
    However, I should have "Maroochydore State High School" in there as well.
    I'm a bit confused here,... are you getting one too many records or one is missing?

    Have you asked your lecturer?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Aug 2009
    Location
    Brisbane, Australia
    Posts
    3
    Quote Originally Posted by StarTrekker
    I'm a bit confused here,... are you getting one too many records or one is missing?
    I'm missing one. What I meant is that compared to the full query, when I run the first sub query separately, I have an extra value. (it shows up with 9 values, while the full query only shows 8)

    Quote Originally Posted by StarTrekker
    Have you asked your lecturer?
    Not yet (I don't see him again 'til Thurs), but he doesn't seem to explain things all that clearly. I'm not being mean ... it's the honest truth. When he explains what we're doing, I don't really understand any of what he's saying. I know how to use MS Access (we had to learn it last year at high school), so it's no big deal that I don't understand him too well... but I don't know what I'm doing wrong for this.

    Do you think maybe a UNION function would solve this?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Does that extra value have related points and related results?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I can't tell you why the one row you expect to be in the output isn't there. As StarTrekker is pointing out / asking, there is probably something that isn't in two related tables. Without that, the Inner join will filter out those rows (since an Inner join requires the value to be in both tables). When I have a problem like that, I start at the lowest level and do simple selects on one table at at time to verify the starting values and then if the Joining values exist in the next highest level. Keep going and you should eventually find what is missing and causing the row not to be displayed.

    Here is something to consider about your query.

    You list 5 tables.

    Your requirements are:

    Display a Distinct list of School_names.
    This list must include either:
    The School has someone who competed in the 800m event.
    or
    The school has someone who placed 1.

    Now the question is what tables do you need to do this.

    To find schools that compete in the 800m event you would need:
    tblEvents (contains the Ev_Name and Rec_ID to join with another table)
    tblAthletes (contains Ath_ID to join to Rec_ID of tbl_Events table and School_Code to join to tblSchools table)
    tblSchools (contains School_name that you want returned and School_code to join with tblAthletes).

    As a skeleton that would be:

    (return School_name) tblSchools (Join School_Code) tblAthletes (Join Ath_ID/Rec_ID) tblEventes (Ev_name = '800m')

    To find schools that who placed 1 you would need:
    tblResults (contains Place and Ath_ID to join to another table)
    tblAthletes (contains Ath_ID to join to tblResults and School_Code to join to tblSchools table)
    TblSchools (contains School_name that you want returned and School_code to join with tblAthletes)

    As a skeleton that would be:

    (return School_name) tblSchools (join school_code) tblAthletes (join Ath_id) tblResults (Place = '1')

    From the above, you only need 4 of the 5 tables. tblPoints is only useful if you want to Display / Filter on Points (which you don't). Even though it is not needed to get the result you want, it is included in your query and and causing the database to do extra processing for no useful reason.

    Do this help you find a possible solution?

Posting Permissions

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