SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: sql query help

  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query help

    I have two tables...

    Table Scores
    Date, TeamA, ScoreA, TeamB, ScoreB

    Table Team
    TeamName, TeamDivision

    I want to select all scores from the Scores table that meet this criteria:
    Both teams in a game must be from Division A (denoted in the Team table).

    How would I make this query?

  2. #2
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure if this is want you need
    Code:
    SELECT ScoreA, ScoreB FROM SCORES,TEAM WHERE TeamDivision='Division A'
    If were you I'd change the definition of Score to

    Table Scores
    Date, TeamID, Score

    and create this new one,

    Table Team Names
    TeamID Name_of_the_Team (ie A or B)

    This would be useful if you are planning to add more teams.

    Paul

  3. #3
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    TeamA is the visiting team... teamB is the home team...

    That's not the specific team name... its the field name

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is really just like that other one you asked about
    Code:
    select GameDate
         , A.TeamName, ScoreA
         , B.TeamName, ScoreB
      from Scores
    inner 
      join Team A
        on TeamA = A.TeamName 
       and         A.TeamDivision = 'A'
    inner 
      join Team B
        on TeamB = B.TeamName 
       and         B.TeamDivision = 'A'
    assumption: TeamA and TeamB columns in Score table match TeamName in team table -- usually, you use numbers or codes as keys, rather than long character fields (names)

    also, don't use Date as a column name -- this was your last warning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •