title: MySQL join on three tables EAV model
body: <p>Kindly consider the following tables:</p>
  
  <p><strong>invoices</strong></p>
  
  <pre><code>+-----------+----+------------+--------+---------+
  | accountid | id | customerid | total  | balance |
  +-----------+----+------------+--------+---------+
  | 1         | 2  | 167909     | 120060 | 120060  |
  +-----------+----+------------+--------+---------+
  </code></pre>
  
  <p><strong>invoices_attributes</strong></p>
  
  <pre><code>+-----------+----+--------------+
  | accountid | id | name         |
  +-----------+----+--------------+
  | 1         | 1  | registration |
  +-----------+----+--------------+
  | 1         | 2  | claimnumber  |
  +-----------+----+--------------+
  | 1         | 3  | jobid        |
  +-----------+----+--------------+
  </code></pre>
  
  <p><strong>invoices_attributes_values</strong></p>
  
  <pre><code>+------------------+-------------+-----------+---------------+
  | attributevalueid | attributeid | invoiceid | value         |
  +------------------+-------------+-----------+---------------+
  | 1                | 1           | 2         | ABC 126L      |
  +------------------+-------------+-----------+---------------+
  | 2                | 2           | 2         | ABZ123        |
  +------------------+-------------+-----------+---------------+
  | 3                | 3           | 2         | MARY DOE      |
  +------------------+-------------+-----------+---------------+
  </code></pre>
  
  <p>Through the help of <a href="https://stackoverflow.com/users/1100552/eugen-rieck">Eugen Rieck's</a> original answer I was able to make the following query </p>
  
  <pre><code>SELECT
    invoices.accountid,
    invoices.id AS invoiceid,
    invoices.customerid,
    invoices.total,
    registration.value AS registration,
    claimnumber.value AS claimnumber,
    jobid.value as jobid
    FROM
    invoices
    LEFT JOIN invoice_attributes ON invoices.accountid=invoice_attributes.accountid
    LEFT JOIN invoice_attribute_values AS registration ON registration.attributeid = invoice_attributes.id AND invoices.id = registration.invoiceid AND invoice_attributes.name = 'registration'
    LEFT JOIN invoice_attribute_values AS claimnumber ON claimnumber.attributeid = invoice_attributes.id AND invoices.id = claimnumber.invoiceid AND invoice_attributes.name = 'claimnumber'
    LEFT JOIN invoice_attribute_values AS jobid ON jobid.attributeid = invoice_attributes.id AND invoices.id = jobid.invoiceid AND invoice_attributes.name = 'jobid'
  </code></pre>
  
  <p>Which gave the following result</p>
  
  <pre><code>+-----------+-----------+------------+--------+--------------+-------------+----------+
  | accountid | invoiceid | customerid | total  | registration | claimnumber | jobid    |
  +-----------+-----------+------------+--------+--------------+-------------+----------+
  | 1         | 2         | 167909     | 120060 | NULL         | NULL        | MARY DOE |
  +-----------+-----------+------------+--------+--------------+-------------+----------+
  | 1         | 2         | 167909     | 120060 | NULL         | ABZ123      | NULL     |
  +-----------+-----------+------------+--------+--------------+-------------+----------+
  | 1         | 2         | 167909     | 120060 | ABC 126L     | NULL        | NULL     |
  +-----------+-----------+------------+--------+--------------+-------------+----------+
  </code></pre>
  
  <p>When I GROUP BY invoices.id some of the columns (registration, claimnumner or job) will become NULL. I desire the result to be as:</p>
  
  <pre><code>+-----------+-----------+------------+--------+--------------+-------------+----------+
  | accountid | invoiceid | customerid | total  | registration | claimnumber | jobid    |
  +-----------+-----------+------------+--------+--------------+-------------+----------+
  | 1         | 2         | 167909     | 120060 | ABC 126L     | ABZ123      | MARY DOE |
  +-----------+-----------+------------+--------+--------------+-------------+----------+
  </code></pre>
  
  <p>How can the query be modified to get the result above?</p>