title: Getting around MySQL "Can't reopen table" error
body: <p>I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on.</p>
  
  <p>The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN</p>
  
  <p>This basically looks like:</p>
  
  <pre><code>SELECT
      *
  FROM search
  INNER JOIN search f1 ON f1.baseID = search.baseID AND f1.condition = condition1
  INNER JOIN search f2 ON f2.baseID = search.baseID AND f2.condition = condition2
  ...
  INNER JOIN search fN ON fN.baseID = search.baseID AND fN.condition = conditionN
  </code></pre>
  
  <p>This works but I would much prefer the "search" table to be temporary (it can be several orders of magnitude smaller if it isn't a normal table) but that gives me a very annoying error: <code>Can't reopen table</code></p>
  
  <p>Some research leads me to <a href="http://bugs.mysql.com/bug.php?id=10327" rel="noreferrer">this bug report</a> but the folks over at MySQL don't seem to care that such a basic feature (using a table more than once) does not work with temporary tables. I'm running into a lot of scalability problems with this issue.</p>
  
  <p>Is there any viable workaround that does not require me to manage potentially lots of temporary but very real tables or make me maintain a huge table with all the data in it?</p>
  
  <p>Kind regards, Kris</p>
  
  <p>[additional]</p>
  
  <p>The GROUP_CONCAT answer does not work in my situation because my conditions are multiple columns in specific order, it would make ORs out of what I need to be ANDs. However, It did help me solve an earlier problem so now the table, temp or not, is no longer required. We were just thinking too generic for our problem. The entire application of filters has now been brought back from around a minute to well under a quarter of a second.</p>