Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Getting Wrong ID on Query

    I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:

    If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.

    For instance, this code:
    Code:
    sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
    sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"
    Set rs = conn.Execute(sql)
    Do While Not rs.EOF
            lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")"
            rs.MoveNext
        Loop
        Set rs = Nothing
    could show the following:
    2441-John Doe (10)
    2441-John Doe (11)
    if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.

    I appreciate any help you can give me!

    Thanks~

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do you know it's two John Does in the r table?

    to me, it looks like one John Doe in the r table with two matching rows in the g table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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