Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    "Doubling" the rows on a view

    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?

    Hartti

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hartti
    2) is there a better approach than this duplicate rows in a view approach?
    yes, there is -- use a UNION query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Thanks, that seems indeed to be a better approach! (haven't built the query yet, but after quickly scanning through the reference manual, I think I will get that done quite painlessly...)

    Hartti

  4. #4
    Join Date
    Apr 2009
    Posts
    3
    Yep, worked like a charm! thanks r937!

    Hartti

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •