LEFT JOIN, INNER OUTER JOIN, LEFT OUTER JOIN is driving me crazy. Please help?

Question

fellow coders! I an having a hard time JOIN(ing) tables.

I have a a lot of users on my site and many of them have the same last names. I am trying to get their emails by their last name. WP stores emails in the users table and user names in the usermeta and I have been trying to use JOIN to get what I want, but I don't understand how it works and I am about to give up for the day. This is what i have after 4.5 hours.

$usersemails = $wpdb->get_results("SELECT users.user_email, usermeta.meta_value 
FROM $wpdb->users
LEFT JOIN $wpdb->usermeta ON $wpdb->users.ID = $wpdb->usermeta.user_id 
    WHERE user_status = '0' 
    AND meta_value = 'Smith'");

Any help is appreciated, so can you please help? Thanks.

Answer 1

Hi @Holidaymaine:

Not sure where you are doing wrong, but try the following instead which is a self contained test.php file you can drop into the root of your website and load in your browser with http://yoursite.com/test.php (assuming you replace yoursite.com with your site's domain! Also, note how I adding in a WHERE {$wpdb->usermeta}.meta_key = 'last_name' to ensure you did not get records you were not expecting):

<?php
/*
Filename: test.php
*/

include('wp-load.php');

$sql =<<<SQL
SELECT
  {$wpdb->users}.user_email,
  {$wpdb->usermeta}.meta_value
FROM
  {$wpdb->users}
  LEFT JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID = {$wpdb->usermeta}.user_id
WHERE 1=1
  AND {$wpdb->users}.user_status = '0'
  AND {$wpdb->usermeta}.meta_key = 'last_name'
  AND {$wpdb->usermeta}.meta_value = 'Smith'
SQL;
  $usersemails = $wpdb->get_results($sql);
  header('Content-type:text/plain');
  print_r($usersemails);

Answer 2

You can do this via API, user search got improved in 3.1

$search = new WP_User_Query(array(
             'meta_value' => 'Lastname',
             'fields' => array('user_email'),
                ));

var_dump($search->get_results());

Answer 3

SELECT users.user_email, usermeta.meta_value

Should be:

SELECT $wpdb->users.user_email, $wpdb->usermeta.meta_value

Prefixes matter.