title: Correlated Subquery in a MySQL CASE Statement
body: <p>Here is a brief explanation of what I'm trying to accomplish; my query follows below.</p>
  
  <p>There are 4 tables and 1 view which are relevant for this particular query (sorry the names look messy, but they follow a strict convention that would make sense if you saw the full list):</p>
  
  <p><code>Performance</code>s may have many <code>Performer</code>s, and those associations are stored in <code>PPerformer</code>. <code>Fan</code>s can have favorites, which are stored in <code>Favorite_Performer</code>. The <code>_UpcomingPerformances</code> view contains all the information needed to display a user-friendly list of upcoming performances.</p>
  
  <p>My goal is to select all the data from _UpcomingPerformances, then include one additional column that specifies whether the given Performance has a Performer which the Fan added as their favorite. This involves selecting the list of Performers associated with the Performance, and also the list of Performers who are in Favorite_Performer for that Fan, and intersecting the two arrays to determine if anything is in common.</p>
  
  <p>When I execute the below query, I get the error <code>#1054 - Unknown column 'up.pID' in 'where clause'</code>. I suspect it's somehow related to a misuse of <a href="http://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html" rel="nofollow">Correlated Subqueries</a> but as far as I can tell what I'm doing should work. It works when I replace <code>up.pID</code> (in the WHERE clause of <code>t2</code>) with a hard-coded number, and yes, pID is an existing column of _UpcomingPerformances.</p>
  
  <p>Thanks for any help you can provide.</p>
  
  <pre><code>SELECT
      up.*,
      CASE
          WHEN EXISTS (
              SELECT * FROM (
                  SELECT RID FROM Favorite_Performer
                  WHERE FanID = 107
              ) t1
              INNER JOIN
              (
                  SELECT r.ID as RID
                  FROM PPerformer pr
                  JOIN Performer r ON r.ID = pr.Performer_ID
                  WHERE pr.Performance_ID = up.pID
              ) t2
              ON t1.RID = t2.RID
          )
          THEN "yes"
          ELSE "no"
      END as pText
  FROM
      _UpcomingPerformances up
  </code></pre>