title: MySQL JOIN when one table's rows must be appended to columns in result
body: <p>I have a table of this form, which captures registrations to events:</p>
  
  <pre><code>id|name|email|eventid
  </code></pre>
  
  <p>Call it t1</p>
  
  <p>There is also a table of custom fields used to add additional info for a registration. The custom field table is</p>
  
  <pre><code>id|customfield_name|customfield_value|parentid
  </code></pre>
  
  <p>Call it t2</p>
  
  <p>So if I have 5 additional custom fields for the event registration, for a single registration in t1, there would be 5 separate rows in t2.</p>
  
  <p>parentid refers to id in first table.</p>
  
  <p>If I do an inner join, something like this, </p>
  
  <pre><code>SELECT t1.name, t2.customfield_value
  FROM table1 t1
  JOIN table2 t2
  ON t2.parentid = t1.id
  WHERE t1.eventid = 2 AND t2.customfield_name='some custom field'
  </code></pre>
  
  <p>then the result is ok. I see name from t1 and one custom field from t2 side by side. But I want to be able to display more than one data_name as a col. So that the result would be something like</p>
  
  <pre><code>name|custom field 1|custom field 2|etc
  </code></pre>
  
  <p>I can not figure out how to use JOIN syntax to accomplish this. I am not well versed in MySQL and am hoping someone can point me in the right direction. </p>
  
  <p>Edit:</p>
  
  <p>Ok Strawberry, here is the sqlfiddle:</p>
  
  <p><a href="http://sqlfiddle.com/#!9/37172/1" rel="nofollow">http://sqlfiddle.com/#!9/37172/1</a></p>
  
  <p>If joining with one customfield, the result is almost what I am looking for, except I would want the column label to be the name of the customfield, and not 'value'. I am trying to figure out how to produce this result, which corresponds to a joining of the registrations table where eventid=2, and the customfields that correspond to the registrations for that eventid. So the desired result is</p>
  
  <pre><code>name  | workphone | mobile | nationality
  Name1   112233      223344   US
  Name2   334455      445566   DE
  </code></pre>
  
  <p>Thanks!</p>