Query in two different tables in Wordpress

Question

I have a code, where it returns me some data of all users of the table wp_usermeta, ( in case I’m only pulling the full Name, and the Capabilities, but to get more complete my table, I would like to add the users email, which is in the table wp_users , how can I make a query to take the values of both tables? Follows code:

<?php
global $wpdb;

$sql = "
  SELECT user_id,meta_key,meta_value
  FROM {$wpdb->usermeta} 
  WHERE ({$wpdb->usermeta}.meta_key = 'first_name' OR {$wpdb->usermeta}.meta_key = 'last_name' OR {$wpdb->usermeta}.meta_key = 'wp_capabilities')";
$ansatte = $wpdb->get_results($sql);

$users = array();
foreach ($ansatte as $a) {
  $users[$a->user_id][$a->meta_key] = $a->meta_value;
}

foreach ($users as $u) {
    echo $u['first_name'].' '.$u['last_name'].' '.$u['wp_capabilities'].'<br>';
}
?>

Answer 1

You do not need to make a query via SQL directly for this. You can use get_users to get a list, and get_user_by() if you want a specific user.

Both return objects WP_User that has all the information you are looking for.

Answer 2

You need to make a Join between the tables:

This query returns the column you want:

SELECT um.user_id, um.meta_key, um.meta_value, u.user_email
FROM wp_usermeta um 
       inner join wp_users u
          on u.ID = um.user_id
  WHERE meta_key = 'first_name' 
     OR meta_key = 'last_name' 
     OR meta_key = 'wp_capabilities'