title: MySQL - Filter Column Multiple Times
body: <p>I'm exporting content from an old, low-adoption CMS. I'm getting stuck linking some information related to my main resources.</p>
  
  <p>One main table, "resources," has books, etc. listed with titles, year of publication, etc. I want to create a table with one record per resource, plus related info from other tables in new columns.</p>
  
  <p>Another table, ResourceNameInts, links the resources table to multiple types of metadata (i.e. authors, language, formats). These are just ID #s that link to a table of controlled names ("ControlledNames"). There are also multiple instances of each data type for each title in resources. (For example, multiple authors, publishers, and formats.) So a book can have one line in the resources table and multiple relationships in ResourceNameInts. For now I'll just group them together with GROUP_CONCAT; I could parse those later if I want.</p>
  
  <p>I want to create multiple columns filtering ResourceNameInts but I can only get one column to work (i.e. creating a "Publishers" column based on ID# 19):</p>
  
  <pre><code>SELECT r1.title, GROUP_CONCAT(c.ControlledName SEPARATOR '; ')
  FROM resources r1
  RIGHT JOIN resourcenameints r2 ON r1.ResourceId = r2.resourceid
  RIGHT JOIN controllednames c ON c.ControlledNameId = r2.ControlledNameId
  WHERE c.FieldId=19
  GROUP BY r1.title
  </code></pre>
  
  <p>I want to create a column for each data type (one for ID#19, then one each for #s 20, 23, 24, 25, and 26). But I can only use one WHERE statement. I've tried using IF or CASE for each column instead, for example:</p>
  
  <pre><code>SELECT r1.title,
  (CASE c.fieldid WHEN 19 THEN GROUP_CONCAT(c.ControlledName SEPARATOR '; ') END) AS publisher,
  (CASE c.fieldid WHEN 20 THEN GROUP_CONCAT(c.ControlledName SEPARATOR '; ') END) AS author
  FROM resources r1
  RIGHT JOIN resourcenameints r2 ON r1.ResourceId = r2.resourceid
  RIGHT JOIN controllednames c ON c.ControlledNameId = r2.ControlledNameId
  WHERE c.FieldId=19 OR c.FieldId=20
  GROUP BY r1.title
  </code></pre>
  
  <p>...but I'm not getting the right results - the "publisher" column is populated but the other columns show up as NULL. I also tried a nested SELECT query:</p>
  
  <pre><code>SELECT r1.title,
  (SELECT GROUP_CONCAT(c.ControlledName SEPARATOR '; ') WHERE c.FieldId=19 GROUP BY r1.title) AS publisher,
  (SELECT GROUP_CONCAT(c.ControlledName SEPARATOR '; ') WHERE c.FieldId=20 GROUP BY r1.title) AS creator
  FROM resources r1 RIGHT JOIN resourcenameints r2 ON r1.ResourceId = r2.resourceid RIGHT JOIN controllednames c ON c.ControlledNameId = r2.ControlledNameId
  GROUP BY r1.title 
  </code></pre>
  
  <p>but that doesn't seem to filter the columns correctly.</p>
  
  <p>Do I need additional syntax for IF or CASE, or is there another technique I don't know about?</p>