Friday, February 24, 2012

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:

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?change != in last line to <

No comments:

Post a Comment