[wp-hackers] Select from meta with a specific value or no meta

[Lox]

2011/5/24 Lox <lox.dev at knc.nc>

> SELECT * FROM wp_users
> LEFT JOIN  wp_usermeta ON wp_users.ID = wp_usermeta.user_id
> WHERE wp_usermeta.meta_key = 'i_dont_exist' AND wp_usermeta.meta_value IS
> NULL
>
> That should return all users but I get not results ...
>

Found it, the meta_key must be in the ON statement:

SELECT * FROM wp_users
LEFT JOIN  wp_usermeta
ON wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key =
'i_dont_exist'
WHERE wp_usermeta.meta_value IS NULL

Thus the followinf query return all users having a meta
'newsletter_subscribe' set to '0' or the users who do not have that meta:

SELECT * FROM wp_users
LEFT JOIN  wp_usermeta  ON  wp_users.ID = wp_usermeta.user_id AND
wp_usermeta.meta_key = 'newsletter_subscribe'
WHERE  (  wp_usermeta.meta_value IS NULL OR wp_usermeta.meta_value LIKE '0')

Cheers

-- 
Lox
lox.dev at knc.nc