title: MySQL EAV SELECT one-to-many single row results
body: <p>I've seen similar questions on here, but haven't been able to find one that matches my specific scenario.  I have the following three tables:</p>
  
  <pre><code>content
  id, [other fields]
  
  attributes
  id, title
  
  attributes_values
  id, attribute_id[foreign to attributes.id], content_id[foreign to content.id], value
  </code></pre>
  
  <p>What I'd like is a single query (or group of sub-queries) that can return the appropriate data to avoid having to manipulate this in the programming.</p>
  
  <p>A little more info about the tables: attributes has three records (Model #, Part #, and Show in Cart).</p>
  
  <p>I need to select all records from content where the attribute_value for Show in Cart = 1. Along with this, I'd like the other related attribute_values to be returned as their related attribute.title. The results would look something like this:</p>
  
  <pre><code>id    [other fields]    Model # [from attributes.title field]    Part # [from attributes.title field]
  1     [any data]        value from attributes_values             value from attributes_values
  2     [any data]        value from attributes_values             value from attributes_value
  </code></pre>
  
  <p>So far, my only way of accomplishing this is not very elegant as I'm having to do multiple joins to the same table:</p>
  
  <pre><code>SELECT * FROM content AS a
  LEFT JOIN attributes_values AS b ON (b.content_id = a.id AND b.attribute_id = [Model # ID])
  LEFT JOIN attributes_values AS c ON (c.content_id = a.id AND c.attribute_id = [Part # ID])
  WHERE a.id IN (
      SELECT content_id FROM attributes_values WHERE attribute_id = [Show in Cart ID] AND value = 1
  )
  </code></pre>
  
  <p>As you can see, I'm having to hard-code related keys into the JOIN statements, which doesn't make this very scalable (and just feels dirty).</p>
  
  <p>Some last few notes: I'm using PHP and Joomla!, so if that would affect your answer (or if there's some secret Joomla tip), feel free to include it.  Also, this is a pre-established table schema that I'm not able to alter, so I need a query solution for the above mentioned tables, not a suggestion on a better table design.</p>
  
  <p>Any help will be greatly appreciated.</p>
  
  <p>Cheers!</p>