title: MySQL CASE Statement in SELECT
body: <p>I have a MySQL query question. I built a table that keeps track of high school football scores. It looks like this:</p>
  
  <pre><code>CREATE TABLE `games` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `home_team` int(11) NOT NULL,
    `visitor_team` int(11) NOT NULL,
    `home_score` tinyint(4) NOT NULL,
    `visitor_score` tinyint(4) NOT NULL,
    `quarter` tinyint(4) NOT NULL,
    `week` tinyint(2) NOT NULL,
    `game_date` date NOT NULL,
    `game_time` time NOT NULL,
    `complete` tinyint(4) NOT NULL DEFAULT '0',
    `stadium_id` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=151;
  </code></pre>
  
  <p>The query that I am doing uses a team's id to compare with. I want to retrieve the id values of the teams that the given team id defeated only. Here is my query so far.</p>
  
  <pre><code>SELECT 
      CASE 
          WHEN games.home_team != ? AND (games.home_score &gt; games.visitor_score) THEN games.home_team 
          WHEN games.visitor_team != ? AND (games.home_score &lt; games.visitor_score) THEN games.visitor_team 
      END AS id, teams.class_id, classes.name 
  FROM games 
      INNER JOIN teams ON id = teams.id 
      INNER JOIN classes ON teams.class_id = class.id
      WHERE games.complete = 1
  </code></pre>
  
  <p>When I run this query in PHP MyAdmin I get the following error:</p>
  
  <pre><code>#1052 - Column 'id' in on clause is ambiguous
  </code></pre>
  
  <p>I am using PDO's execute() so the ?'s represent team id values. I figured since I assigned the result of my CASE statements to id, then I could use it to join the teams table. Can anybody point me in the right direction to fix this error? Thank you in advance.</p>