I have a table listing game results. In games there are two teams (team1 & team2) involved and the table contains score for both of them. The database contains games between many different teams.
What I am trying to accomplish is to create summaries for each of the teams. As a team can be either team1 or team2 in any of the games played, creating a single query to find all games for one team is a little complicated (not impossible :-)
I thought that it might make things simpler if I create a view which contains all games twice. The difference between the duplicate rows is that team1 and team2 have switched places.
1) what is the pseudo SQL code to do this (my initial attempts were not very successful and currently I trying a route where I create a temporary view which is a copy of my games table, although the teams have been reversed in the view... unfortunately I have a small roadblock in here as well :-)
2) is there a better approach than this duplicate rows in a view approach?