title: MySQL - where clause on triple nested select
body: <p>I'm writing the most complicated query I've personally ever done... and I'm stuck!</p>
  
  <p>Here's the code:</p>
  
  <pre><code>SELECT 
      el.UID, 
      (
          SELECT 
              SUM(booked)  
          FROM 
              (
                  SELECT 
                  ts.*, max(ts.dateStored) 
                  FROM eventsDetails ed 
                  JOIN ticketSales ts ON ts.DUID = ed.UID 
                  WHERE ed.event_masterEvent = 1147 
                  GROUP BY ts.DUID
              ) a 
      ) as booked,
      el.event_confirmed, 
      el.event_title, 
      (
          SELECT 
              GROUP_CONCAT(g.colour) 
          FROM eventsGenre eg 
          JOIN genres g ON g.UID = eg.GUID 
          WHERE eg.EUID = el.UID
      ) AS genreCodes, 
      (
          SELECT 
              GROUP_CONCAT(g.genre) 
          FROM eventsGenre eg 
          JOIN genres g ON g.UID = eg.GUID 
          WHERE eg.EUID = el.UID
      ) AS genreNames 
  FROM eventsList el 
  JOIN eventsDetails ed ON el.UID = ed.event_masterEvent 
  WHERE el.event_active='1' 
  AND ed.event_eventDateAndTime &gt;= CURDATE() 
  GROUP BY el.UID 
  ORDER BY ed.event_eventDateAndTime ASC
  </code></pre>
  
  <p>On the line "WHERE ed.event_masterEvent = 1147", I wan't to change that to "WHERE ed.event_masterEvent = el.UID". 1147 was just to test that the code works... but now I need to reference the overall event ID.</p>
  
  <p>Whenever I do that, I get an error saying the column doesn't exist.</p>
  
  <p>I don't understand, because further down the query, I am able to say "WHERE eg.EUID = el.UID" within a sub select.</p>
  
  <p>I don't know what I'm doing wrong... as I said before, I'm very much out of my comfort zone at the moment and looking for some MySQL gurus to ride in and explain where I've gone wrong.</p>
  
  <p>I've got a feeling I need to add another join somewhere, but I can't work out where. I tried to search for the answer, but I'm not really sure what search terms to use - so I'm not getting very far.</p>
  
  <p>Any help would be greatly appreciated!</p>
  
  <p>Here's a fiddle of the issue: <a href="http://sqlfiddle.com/#!9/c77a98/2" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/c77a98/2</a></p>