[wp-hackers] User Meta Query Assistance

[Mike Walsh]

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