title: Wordpress SQL query the user's first & last name for product X?
body: <p>I am creating an SQL query where I want to display the full names and emails of customers who have purchased a downloadable product from a woocommerce shop. I am looking for something like this:</p>
  
  <pre><code>user_id    first_name    last_name    user_email    order
  ---------------------------------------------------------
  1          Peter         Jones        a12@gmail     Doc_1
  </code></pre>
  
  <p>In the table above, <strong>Doc_1</strong> stands for the name of the product, in this case the customer with <strong>user_id = 1</strong> can download <strong>Doc_1</strong>. For wordpress users, you know that the table <code>wp_usermeta</code> contains all the user's information, and looks like this:</p>
  
  <pre><code>umeta_id    user_id    meta_key    meta_value
  ---------------------------------------------
  1           1          nickname    petjon123
  2           1          first_name  Peter
  3           1          last_name   Jones
  ...         ...        ...         ...
  </code></pre>
  
  <p>This list goes down containing more <code>meta_value[s]</code> including email, etc. Everything would have been much more easier if the <code>meta_key[s]</code> would be table headers showing <code>first_name</code> and <code>last_name</code> beside each other instead of on top one another. I have come this far where my query shows the <code>first_name</code>, but I am having trouble getting the <code>last_name</code>. My SQL query looks like this:</p>
  
  <pre><code>SELECT
    wp_usermeta.user_id,
    wp_usermeta.meta_value AS 'first_name',
    Wp_woocommerce_downloadable_product_permissions_1.user_email AS 'email',
    Wp_woocommerce_order_items_1.order_item_name AS 'order'
  FROM Wp_woocommerce_order_items
       INNER JOIN Wp_woocommerce_downloadable_product_permissions
         ON Wp_woocommerce_order_items.order_id = Wp_woocommerce_downloadable_product_permissions.order_id,
       (Wp_woocommerce_order_items AS Wp_woocommerce_order_items_1
       INNER JOIN Wp_woocommerce_downloadable_product_permissions AS Wp_woocommerce_downloadable_product_permissions_1
         ON Wp_woocommerce_order_items_1.order_id = Wp_woocommerce_downloadable_product_permissions_1.order_id)
       INNER JOIN wp_usermeta
         ON Wp_woocommerce_downloadable_product_permissions_1.user_id = wp_usermeta.user_id
  GROUP BY wp_usermeta.user_id,
           wp_usermeta.meta_value,
           Wp_woocommerce_downloadable_product_permissions_1.user_email,
           Wp_woocommerce_order_items_1.order_item_name,
           Wp_woocommerce_order_items_1.order_item_type,
           wp_usermeta.meta_key
  HAVING (((Wp_woocommerce_order_items_1.order_item_type) = 'line_item')
  AND ((wp_usermeta.meta_key) = 'first_name'));
  </code></pre>
  
  <p>Giving me this result:</p>
  
  <pre><code>user_id    first_name    user_email    order
  --------------------------------------------
  1          Peter         a12@gmail     Doc_1
  </code></pre>
  
  <p>I checked these values manually, and they are correct, however, I must say that if I do not <strong>GROUP</strong> the results, I get 1000 times more results, so <strong>GROUPING</strong> the data simplifies the results by basically removing the duplicates which obviously is excessive. I am doubting that this query is efficient given that the execution time until the results appear is +/-<strong>20368.14 ms</strong> and hereby am very open to any suggestions of improving this code. Having this said, I thought that I had to do another query to get the <code>last_name</code> making this a <strong>NESTED QUERY</strong>. If I open the tables in MS Access, create lets say <strong>Query1</strong> which is the query above, and join <strong>Query1</strong> with the table I get the results that I want. The SQL code for this is as follows:</p>
  
  <pre><code>SELECT
    Query1.user_id,
    Query1.first_name,
    wp_usermeta.meta_value AS 'last_name',
    Query1.user_email,
    Query1.order_item_name
  FROM wp_usermeta
  INNER JOIN Query1
    ON wp_usermeta.user_id = Query1.user_id
  WHERE (((wp_usermeta.meta_value) &lt;&gt; '')
  AND ((wp_usermeta.meta_key) = 'last_name'));
  </code></pre>
  
  <p>I tried substituting <strong>Query1</strong> for this second query using parenthesis by enclosing the code from <strong>Query1</strong>, and this did not work. I get this error:</p>
  
  <p><em>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.user_id, (SELECT wp_usermeta.user_id, wp_usermeta.meta_value AS 'first' at line 22</em></p>
  
  <p>Does anybody know how to do this? Thanks!!</p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>According to the answer provided by @Hogan I came up with this:</p>
  
  <pre><code>SELECT
    first.user_id,
    first.meta_value AS 'first_name',
    last.meta_value AS 'last_name',
    Wp_woocommerce_downloadable_product_permissions_1.user_email,
    Wp_woocommerce_order_items_1.order_item_name
  FROM Wp_woocommerce_order_items
       INNER JOIN Wp_woocommerce_downloadable_product_permissions
         ON Wp_woocommerce_order_items.order_id = Wp_woocommerce_downloadable_product_permissions.order_id,
       wp_usermeta AS last
       INNER JOIN ((Wp_woocommerce_order_items AS Wp_woocommerce_order_items_1
       INNER JOIN Wp_woocommerce_downloadable_product_permissions AS Wp_woocommerce_downloadable_product_permissions_1
         ON Wp_woocommerce_order_items_1.order_id = Wp_woocommerce_downloadable_product_permissions_1.order_id)
       INNER JOIN wp_usermeta AS first
         ON Wp_woocommerce_downloadable_product_permissions_1.user_id = first.user_id)
         ON last.user_id = Wp_woocommerce_downloadable_product_permissions_1.user_id
  GROUP BY first.user_id,
           first.meta_value,
           last.meta_value,
           Wp_woocommerce_downloadable_product_permissions_1.user_email,
           Wp_woocommerce_order_items_1.order_item_name,
           Wp_woocommerce_order_items_1.order_item_type,
           first.meta_key,
           last.meta_key
  HAVING (((Wp_woocommerce_order_items_1.order_item_type) = 'line_item')
  AND ((first.meta_key) = 'first_name')
  AND ((last.meta_key) = 'last_name'));
  </code></pre>
  
  <p>Which works perfectly fine in MS Access but in SQL it does not. I am getting this error:</p>
  
  <p><em>Unknown column 'first.meta_key' in 'having clause'</em></p>
  
  <p>Why?</p>
  
  <p>I also have to mention, that when I add the <code>meta_key[s]</code> to the <code>SELECT</code> like this:</p>
  
  <pre><code>SELECT
    first.user_id,
    first.meta_value AS 'first_name',
    last.meta_value AS 'last_name',
    Wp_woocommerce_downloadable_product_permissions_1.user_email,
    Wp_woocommerce_order_items_1.order_item_name,
    first.meta_key,
    last.meta_key
  </code></pre>
  
  <p>I at least get no error, the query is running but it goes on and on increasing my CPU usage drastically given that I am working on a localhost.</p>
  
  <hr>
  
  <p><strong>SOLUTION</strong></p>
  
  <p><code>SELECT DISTINCT</code> did the trick. No <strong>GROUPING</strong>. The query results appear in <strong>127.76 ms</strong>. This is my query:</p>
  
  <pre><code>SELECT DISTINCT
    first.user_id AS 'id',
    first.meta_value AS 'first_name',
    last.meta_value AS 'last_name',
    Wp_woocommerce_downloadable_product_permissions_1.user_email AS 'email',
    Wp_woocommerce_order_items_1.order_item_name AS 'order'
  FROM Wp_woocommerce_order_items
       INNER JOIN Wp_woocommerce_downloadable_product_permissions
         ON Wp_woocommerce_order_items.order_id = Wp_woocommerce_downloadable_product_permissions.order_id,
       ((Wp_woocommerce_order_items AS Wp_woocommerce_order_items_1
       INNER JOIN Wp_woocommerce_downloadable_product_permissions AS Wp_woocommerce_downloadable_product_permissions_1
         ON Wp_woocommerce_order_items_1.order_id = Wp_woocommerce_downloadable_product_permissions_1.order_id)
       INNER JOIN wp_usermeta AS first
         ON Wp_woocommerce_downloadable_product_permissions_1.user_id = first.user_id)
       INNER JOIN wp_usermeta AS last
         ON Wp_woocommerce_downloadable_product_permissions_1.user_id = last.user_id
  WHERE (((first.meta_key) = 'first_name')
  AND ((last.meta_key) = 'last_name')
  AND ((Wp_woocommerce_order_items_1.order_item_type) = 'line_item'));
  </code></pre>
  
  <p>As @Hogan stated, simply add the table <code>wp_usermeta</code> again with another alias, and make an <code>INNER JOIN</code>.</p>