avoiding duplicate lines

sps · #1 (**permalink**) 11-06-04, 18:16

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?