Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a site with many users and many of them have the same last name. I want to get the emails of all the users with the same last name (IE: Smith) that has a post related to a particular taxonomy term (IE: Baseball).

So far I have this code that works great in getting all the users with the same last name ( thanks to Mike Schinkel ). I suck at using the JOIN function but I am learning and I really need this sooner than later, so I need help.

$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); 

Your time is greatly appreciated and I will pay it forward. Thanks.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Hi @Holidaymaine:

Here's the query you are looking for:

<?php

include( '../wp-load.php' );

$sql =<<<SQL
SELECT DISTINCT
  u.user_email AS user_email,
  um.meta_value AS user_lastname
FROM
  {$wpdb->users} AS u
  LEFT JOIN {$wpdb->usermeta} AS um ON u.ID = um.user_id
  LEFT JOIN {$wpdb->posts} AS p ON u.ID = p.post_author
  LEFT JOIN {$wpdb->term_relationships} AS tr ON p.ID = tr.object_id
  LEFT JOIN {$wpdb->term_taxonomy} AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
  LEFT JOIN {$wpdb->terms} AS t ON tt.term_id = t.term_id
WHERE 1=1
  AND u.user_status = '0'
  AND um.meta_key = 'last_name'
  AND um.meta_value = '%s'
  AND t.slug = '%s'
SQL;
  $sql = $wpdb->prepare( $sql, 'Smith', 'baseball' );
  $usersemails = $wpdb->get_results( $sql );
  header( 'Content-type:text/plain' );
  print_r( $usersemails );
share|improve this answer
    
Thank you Mike. This is perfect and saved me a lot of time and headache. I can never repay you, but as promised I will pay it forward. Thanks again. –  Holidaymaine Apr 23 '11 at 8:11
    
@Holidaymaine - Glad I could help. How to repay me (and others?) Just be sure to come here often, ask great questions, vote often for good answers (even for answers to questions you did not ask), and then remember to come back to select the best answers (as you did for this question.) –  MikeSchinkel Apr 23 '11 at 20:26
    
No problem. Will do. –  Holidaymaine Apr 24 '11 at 2:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.