Wordpress Usermeta Select multiple keys and values

Question

trying to retrieve all user_id's where those user_id's meet a 3 criteria.

the code I have is

$sql = "
SELECT user_id
FROM {$wpdb->usermeta} 
WHERE (({$wpdb->usermeta}.meta_key = 'job_title' AND {$wpdb-
>usermeta}.meta_value = '".$job_title."') OR
({$wpdb->usermeta}.meta_key = 'pmpro_bstate' AND {$wpdb-
>usermeta}.meta_value = '".$state."') OR
({$wpdb->usermeta}.meta_key = 'pmpro_baddress2' AND {$wpdb-
>usermeta}.meta_value = '".$suburb."'))
GROUP BY
   user_id
HAVING 
   COUNT(DISTINCT meta_key)=3";

Is Lower() needed on the value and variables to compare them accurately ?

And is there a more efficient way to perform this query ?

Cheers

Answer 1

It is much better to do this using INNER JOIN:

$sql = "SELECT u.ID
FROM wp_users u
INNER JOIN wp_usermeta m1 ON u.ID = m1.user_id AND m1.meta_key = 'job_title'
INNER JOIN wp_usermeta m2 ON u.ID = m2.user_id AND m2.meta_key = 'pmpro_bstate'
INNER JOIN wp_usermeta m3 ON u.ID = m3.user_id AND m3.meta_key = 'pmpro_baddress2'
WHERE m1.meta_value = '".$job_title."'
AND m2.meta_value = '".$state."'
AND m3.meta_value = '".$suburb."'"