I'm trying to write a SQL statement to get all of the email addresses from a WordPress DB from users within the US. Disclaimer, I have never written a SQL statement on my own. I've only modified other SQL statements. While this would be a simple SQL statement if the email and country were in separate columns of one DB table, they are stored in key/value pairs. I've attached a screenshot to illustrate. I apologize in advance if this is a stupid question.

I tried

SELECT meta_key.billing_email FROM `wp_xk_usermeta` WHERE meta_key.billing_country = "US"; 

I was hoping to get a list of all emails from people in the US.

CodePudding user response：

SQL is not correct , try using this :

SELECT meta_value
FROM wp_xk_usermeta
WHERE meta_key = 'billing_email' AND meta_value IN (
  SELECT meta_value
  FROM wp_xk_usermeta
  WHERE meta_key = 'billing_country' AND meta_value = 'US'
);

CodePudding user response：

If you have the following data in wp_xk_usermeta -

the following query -

SELECT `um1`.`meta_value` `billing_email`
FROM `wp_xk_usermeta` `um1`
JOIN `wp_xk_usermeta` `um2`
    ON `um2`.`user_id` = `um1`.`user_id`
    AND `um2`.`meta_key` = 'billing_country'
    AND `um2`.`meta_value` = 'US'
WHERE `um1`.`meta_key` = 'billing_email';

will return -