Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: Query design based on many-to-many

    Run into a design problem with a query. In the spirit of the season, I will describe the issue in an easy to understand example. Say I have a database based on the Indianapolis Colts football season. My main table is game information. (Week number) is the primary key. There are 17 entries. The many-to-many relationship is a table based on scoring.

    MAIN TABLE
    (Week #) primary key
    Date
    Location
    Attendance
    Field Condition

    SECOND TABLE
    (Week Score ID) primary key
    Week ID related to (Week #) in Main Table
    Player Name
    Points
    Description of scoring play

    I want to run a query that will pull up only the weeks that my Running Back scores a touchdown. If my running back scores 3 touchdowns in a game which would mean three entries in my second table, I only want my query to Indicate one week #.

    This description seemed so easy at first...... Hopefully this is understandable, I think there is probably a very easy solution.
    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select distinct
           M.WeekNo
      from secondtable S
    inner
      join maintable M
        on S.WeekNo = M.WeekNo
     where S.PlayerName = 'Edgerrin James'
       and S.DescriptionOfScoringPlay = 'touchdown'
    good luck in the frigid confines of gillette stadium

    i'm calling eagles and pats in houston
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by pstickne
    DISTINCT?

    see:
    http://www.1keydata.com/sql/sqldistinct.html
    what's your question, pstickne?

    by the way, that is a horrible explanation of DISTINCT:
    The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundencies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL.
    SELECT does not necessarily mean there will be redundancies

    "select each DISTINCT element" ???

    no wonder we get people posting questions like
    i have SELECT DISTINCT department, employee... but it doesn't work, it returns duplicated departments, i want to see the department only once!!!"
    1keydata's tutorial could use an overhaul
    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
  •