[wp-hackers] User Meta Query Assistance

[DD32]

This works for me:

SELECT ID, user_login, fname, lname
FROM `wp_users` 

LEFT JOIN (
	SELECT user_id AS uid, meta_value AS fname
	FROM `wp_usermeta` 
	WHERE meta_key = 'first_name'
) AS metaF ON wp_users.ID = metaF.uid
LEFT JOIN (
	SELECT user_id AS uid, meta_value AS lname
	FROM `wp_usermeta` 
	WHERE meta_key = 'last_name'
) AS metaL ON wp_users.ID = metaL.uid

returns:

ID 	user_login 	fname 	lname 
----------------------------------------
1	admin		NULL	NameHere
2	DD32		Dion	NULL
3	VerminUser	Ask	B
4	low		First	Last

On Mon, 14 Jan 2008 16:11:47 +1100, Mike Walsh <mike_walsh at mindspring.com> wrote:

> Thanks for the tip. While this does work for single user, it also confirms
> what I suspected after I sent my e-mail this morning and left the house of
> the day.  I didn't really phrase my question correctly.
>
> What I am looking to do is construct a query which will return records for
> all users and have the first name and last name from the usermeta table
> included in the query results.  Using your example, I tried something like
> this (my Wordpress table prefix is "wp_ga_" instead of "wp_"):
>
> SELECT
> 	(SELECT meta_value from wp_ga_usermeta, wp_ga_users where meta_key =
> 'first_name' AND wp_ga_usermeta.user_id = wp_ga_users.id) as first,
> 	(SELECT meta_value from wp_ga_usermeta, wp_ga_users where meta_key =
> 'last_name' AND wp_ga_usermeta.user_id = wp_ga_users.id) as last,
> 	wp_ga_users.*
> FROM
> 	wp_ga_users, wp_ga_usermeta WHERE wp_ga_users.id =
> wp_ga_usermeta.user_id
>
> Unfortunately the subselect queries don't return a single row so this
> doesn't work.  Any further guidance would be welcome.  Adding the DISTINCT
> keyword to the query didn't help either.
>
> Thanks,
>
> Mike
> ------------------------------
>
> Message: 5
> Date: Sun, 13 Jan 2008 12:15:03 -0500
> From: Williams Phillip <phil.m.williams at gmail.com>
> Subject: Re: [wp-hackers] User Meta Query Assistance
> To: wp-hackers at lists.automattic.com
> Message-ID: <125C1513-20E9-41A3-A2F7-48CC1FAB2D24 at gmail.com>
> Content-Type: text/plain;	charset=US-ASCII;	delsp=yes;
> format=flowed
>
> You need to use a subselect as a scalar operand in your query. So
> something like the following should work:
>
> SELECT
> 	(SELECT meta_value from wp_usermeta where meta_key = 'first_name'
> AND user_id = 2) as first,
> 	(SELECT meta_value from wp_usermeta where meta_key = 'last_name' AND
>
> user_id = 2) as last,
> 	wp_users.*
> FROM
> 	wp_users
> WHERE
> 	wp_users.ID = 2
>
>
> Phil
> phil.m.williams at gmail.com
>
>
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>