Join query on Wordpress usermeta

Question

We all love wordpress right?

So I've had to work with the usermeta table that basically have this structure.

umeta_id | user_id | meta_key | meta_value

For some reasons several in-house developer plugins wrote a lot of additional information on this table and lately I won the duty to work with it and pull out some important information that I need to export and save in a structure that actually make sense for a web application.

To provide an example this is the kind of data that I can find:

umeta_id | user_id | meta_key | meta_value
 1       | 1       | ourID    | asdad878d7a
 2       | 1       | country  | fooland
 3       | 1       | firstname| foo
 4       | 1       | lastname | bar

I would like to create a result that resemble this structure:

user_id  | ourID       | country
 1       | asdad878d7a | fooland

I tried something but really this kind of structure isn't something I'm accustomed too.

This is my query so far:

SELECT meta.umeta_id as umeta_id, meta.user_id as user_id, channel.meta_value as ourID, country.meta_value as country
FROM usermeta as meta
INNER JOIN usermeta as channel
INNER JOIN usermeta as country
WHERE channel.meta_key = 'ourID'
AND country.meta_key = 'country'

But the result is basically something wrong. I'm having lots of duplicate of the same user_id, one for every row assigned to the user_id but only reporting the field selected instead of the value. Now while this make sense I don't know how to correctly write this query.

Something like this:

umeta_id | user_id | ourID       | country 
 1       | 51424   | UC6Y94UM6rj | United Kingdom
 1       | 51424   | UC6Y94UM6rj | Italy
 1       | 51424   | UC6Y94UM6rj | Italy
 1       | 51424   | UC6Y94UM6rj | Italy
 1       | 51424   | UC6Y94UM6rj | Croatia
 1       | 51424   | UC6Y94UM6rj | United States
 1       | 51424   | UC6Y94UM6rj | Croatia

Clearly I'm doing something very wrong and I'm here hoping that someone can help me understand how to run this kind of query properly more than having the query done by someone else.

Answer 1

Would this get you closer to what you are looking for?

SELECT meta.umeta_id as umeta_id, meta.user_id as user_id, meta.meta_value as ourID, country.meta_value as country
FROM usermeta as meta
INNER JOIN usermeta as country ON meta.user_id = country.user_id
WHERE meta.meta_key = 'ourID' 
AND country.meta_key = 'country'

The logic is to first find an entry that has meta_key 'ourID'. The value of user_id and ourID are picked up from this entry, so the inner join is only required to merge the value of country. The ON condition in INNER JOIN selects the correct country entry.