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

Lox lox.dev at knc.nc
Tue May 24 09:55:56 UTC 2011


2011/5/24 Dion Hulse (dd32) <wordpress at dd32.id.au>

> Without actually attempting it myself, the 2 things you need:
>  * The correct join type, Inner join is probably not it. I'm thinking an
> Outer join might be what you're after.. Inner join will only return when
> the
> meta value exists IIRC, Outer joins allow nulls I think.. (I usually try
> half a dozen join syntaxes honestly.. I'm not SQL Guru)
>  *   AND (  CAST(mt1.meta_value AS CHAR) = '1' OR mt1.meta_value IS NULL )
>  * You might need to throw in   OR mt1.meta_value = '' as well.. as long as
> != 1 is 0 or something.. I'm thinking of partial deleted data.. hopefully
> not a problem for you
>

According to
http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.htmlI
should use LEFT JOIN:

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 ...

-- 
Lox
lox.dev at knc.nc


More information about the wp-hackers mailing list