Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Unanswered: avoiding duplicate lines

    I have a oracle database that includes the following tables:

    game(gameID, season, week, stadium) -- gameID is PK

    competesIn(gameID, team, score) -- gameID, teamName is PK

    As you can probably infer, it is a database to track the results of football games.

    competesIn has two records for every game, one for each team.

    I would like to create a view that combines these tables into the following structure

    gameView(season, week, location, team1, score1, team2, score2)

    I have tried using the following SQL inside the view:

    Code:
    select g.season, g.week, g.stadium, 
      c1.team as team1, c1.score as Score1, 
      c2.team as team2, c2.score as Score2 
      from game g, competesIn c1, competesIn c2 
      where c1.gameID = g.gameID 
      and c2.gameID = g.gameID 
      and c1.team != c2.team
    This does create the view I was looking for but it creates two rows for each game and I need it to return only one row per game.

    Any thoughts?

    Steve

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change != in last line to <
    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
  •