Select User by Joining Multiple Meta Value Results

( I would ask this in the general stack exchange forum but I think it is more relevant here. I’m intentionally using direct queries to the database here. )

I’m trying to select a group of users that have two distinct meta key and meta value parameters.

For example, select all usernames and ID for users with both where a meta_key= “key1″ and meta_value=”value1” and also for the same user where another meta_key = “keyA” and meta_value = “valueA”

In otherwords, I have multiple sets of metadata for a user, I’m looking for a specific set of users that meet criteria in two groups of metadata. There is no relation between the two metadata entries, except that they are both applied to the same user.

The challenge that I am having is that I guess I’m trying to left join any metadata entry to the respective user without having to do a double query. Is it possible?

Here’s what I’ve tried:

SELECT wp_users.ID, wp_users.display_name
FROM wp_users LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id AND  
wp_usermeta.meta_value= 'value1'
WHERE  wp_usermeta.meta_key = 'keyA' AND wp_usermeta.meta_value = 'valueA' 

I’ve also tried this:

SELECT wp_users.ID, wp_users.display_name
FROM wp_users LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id   
WHERE (wp_usermeta.meta_key = 'key1' AND wp_usermeta.meta_value= 'value1')
AND ( wp_usermeta.meta_key = 'keyA' AND wp_usermeta.meta_value = 'valueA') 

Both these return empty sets. I suspect that it is only left joining to one usermeta entry and so when I’m telling it to look for the second set of values in the usermeta entry that has been left joined to the user entry that it returns an empty set. My question is how do I filter through all the user meta info so that I can only return users with results that match both. Thanks for looking!

——– UPDATE ————–

This is the final code that worked for me:

SELECT u.ID, u.display_name
FROM wp_users AS u 
LEFT JOIN wp_usermeta AS um1 ON u.ID = um1.user_id 
LEFT JOIN wp_usermeta AS um2 ON u.ID = um2.user_id 
WHERE  um1.meta_key = 'key1' AND um1.meta_value = 'value1' 
AND um2.meta_key = 'keyA' AND um2.meta_value = 'valueA'
LIMIT 0, 60