How to select meta value column based on user id and two meta keys-CodePudding

I want to select values 'b' and 'c' by meta key and user id = 197

Select cc.meta_value, fname,cc.meta_value lname FROM usermeta cc where 
user_id = 197 and (cc.meta_key = 'last_name' or cc.meta_key = 'first_name')

CodePudding user response：

If You want to select them in one query:

select meta_value from YOURTABLENAME where user_id=197 
and meta_key in ('first_name','last_name')

Or if You want to select them separately:

select meta_value from YOURTABLENAME where user_id=197 and meta_key='last_name';
select meta_value from YOURTABLENAME where user_id=197 and meta_key='first_name';

CodePudding user response：

Try this:

In will get you all the values in the specified list

Select cc.meta_value fname,cc.meta_value lname 
FROM usermeta cc where 
user_id = 197 and cc.meta_key in('last_name','first_name')

CodePudding user response：

You could use the second condition in the WHERE clause to achieve what you want. It's important to wrap the second condition inside parentheses. Like this:

SELECT * 
FROM wp_usermeta 
WHERE user_id = 1 
AND 
(
CONVERT(meta_key USING utf8) = 'first_name' 
OR
CONVERT(meta_key USING utf8) = 'last_name'
)

Which outputs this:

If you only need the meta_value column, then you could use this:

SELECT meta_value 
FROM wp_usermeta 
WHERE user_id = 1 
AND 
(
CONVERT(meta_key USING utf8) = 'first_name' 
OR
CONVERT(meta_key USING utf8) = 'last_name'
)

Which outputs this: