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

[Dion Hulse (dd32)]

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

Cheers
Dion

On 24 May 2011 17:36, Lox <lox.dev at knc.nc> wrote:

> Hello,
>
> I need to make a query that selects users having a meta with a specific
> value OR not having that meta. I don't see how to achieve it...
>
> This will return me the users with the meta 'newsletter_optin' set to '1',
> But I also need the users who do have not that meta set.
>
> SELECT ID FROM {$wpdb->users} INNER JOIN {$wpdb->usermeta} ON
> ({$wpdb->users}.ID = {$wpdb->usermeta}.user_id)
> INNER JOIN {$wpdb->usermeta} AS mt1 ON ({$wpdb->users}.ID = mt1.user_id)
> WHERE 1=1
> AND {$wpdb->usermeta}.meta_key = 'newsletter_subscribe'
> AND CAST({$wpdb->usermeta}.meta_value AS CHAR) = '1'
> AND mt1.meta_key = 'newsletter_optin'
> AND CAST(mt1.meta_value AS CHAR) = '1'
>
> Regards.
>
> --
> Lox
> lox.dev at knc.nc
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>