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 <
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment