1

( 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
Improve this question

1 Answer 1

Reset to default
4

Try this one with additional join on metadata

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

Also use group by

Improve this answer
3
  • This seems like it might work, but I may not be implementing it correctly. In your example, 'um1' and 'um2' actually represent the same table 'wp_usermeta' in my situation. Shouldn't I make them distinct aliases of 'wp_usermeta' ? So the LEFT JOIN lines should read something like this: 'LEFT JOIN wp_usermeta um1 ...'
    – Brent
    Oct 1, 2013 at 21:10
  • That did work. I just had to rebuild the code from the ground up, debugging it as I went. Thank you!
    – Brent
    Oct 1, 2013 at 21:46
  • @Brent Sory for being late but i forgot to mention table in the join just used the aliases that was wrong , i have updated my answer
    – M Khalid Junaid
    Oct 3, 2013 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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