Query that joins the postmeta table twice

“My SQL doesn’t work” questions are difficult because they are nearly untestable without having the OP’s dataset, but…

I think you may be making this too complicated.

SELECT 
  DISTINCT (p.ID), 
  p.post_title
FROM wp_posts p
JOIN wp_postmeta pm1 ON p.ID = pm1.post_id
JOIN wp_postmeta pm2 ON p.ID = pm2.post_id
WHERE 1 = 1
AND p.post_type="game"
AND (
  pm1.meta_key =  'year' 
  AND pm1.meta_value="2014"
) 
AND (
  pm2.meta_key IN ('team1','team2')
  AND pm2.meta_value="yankees"
)

I think that may do what you need. And yes, I am pretty sure you can’t do that with WP_Query directly, but…

function swap_key_wpse_144950($where) {
  remove_filter('posts_where','swap_key_wpse_144950');
  $where = str_replace("= 'xxteamsxx'"," IN ('team1','team2')",$where);
  return $where;
}
add_filter('posts_where','swap_key_wpse_144950');

$args = array(
  'meta_query' => array(
    array(
      'key' => 'year',
      'value' => '2014'
    ),
    array(
      'key' => 'xxteamsxx',
      'value' => 'yankees'
    )
  )
);
$q = new WP_Query($args);
var_dump($q->request);