title: Query to find the most played enemy
body: <p>I've realized some sort of online game. Now I'd like do show a statistic where you can see who was the enemy you played most against. </p>
  
  <p>I've stored the games in a database. The players are stored in the fields PLAYER1 and PLAYER2. It depends, which one invites the other one. The one who starts the game and invites the other one is PLAYER1.</p>
  
  <pre><code>ID | PLAYER1 | PLAYER2 | GAMESTATE
  </code></pre>
  
  <p>Now I have certain entries. Let's guess I am player 1 and my friend (2) invited me twice, also I invited him once. The Entries would look as follows:</p>
  
  <pre><code>1  | 2       | 1       | 1
  2  | 2       | 1       | 1
  3  | 1       | 2       | 1
  4  | 3       | 4       | 1  &lt;-- some other random game
  </code></pre>
  
  <p>Now, I'd like to know the player I have played most with. I actually need one select which counts the most played player, but in both cases (PLAYER1 or PLAYER2).</p>
  
  <p>What would be the best solution for one single query? May I use <a href="http://dev.mysql.com/doc/refman/5.1/de/union.html" rel="nofollow"><code>MySQL UNION</code></a> or GROUP BY? If yes, how do I have to do?</p>
  
  <p><strong>EDIT</strong>: Expected result would actually be this:</p>
  
  <pre><code>PLAYER | MOST_ENEMY | GAMES
  1      | 2          | 3
  2      | 1          | 3
  3      | 4          | 1
  4      | 3          | 1
  </code></pre>
  
  <p>Thank you for your help!</p>