Philex Philex - 5 months ago 24
SQL Question

CASE WHEN query not returning expected results

I am trying to get data from the wp_usermeta table of the Wordpress database.

For those who aren't familliar with it, the structure is as follows:

umeta_id | user_id | meta_key | meta_value
1 | 1 | role | admin
2 | 1 | lat | 12.34456
3 | 1 | lng | 9.392933
4 | 2 | role | user
5 | 2 | lat | 8.694840
6 | 2 | lng | 29.32930
7 | 3 | role | subscriber
8 | 3 | lat | 32.32323
9 | 3 | lng | 3.222222
10 | 3 | some key | some value
... and so on


Now I've got an entry with latitude and longitude coordinates inside the table, which are defined by the meta_key being "lat" or "long" and the meta_value being the coordinates.

I'd like to get each user with their lat and long coords, like this:

user_id | lat | lng
1 | 12.34456 | 9.392933
2 | 8.694840 | 29.32930
3 | 32.32323 | 3.222222


For that I am currently using this query:

SELECT umeta.user_id,
max(CASE WHEN umeta.meta_key = "lat" THEN umeta.meta_value END) AS lat,
max(CASE WHEN umeta.meta_key = "lng" THEN umeta.meta_value END) AS lng
FROM wp_usermeta umeta


Which only gives me:

user_id | lat | lng
1 | 12.34456 | 9.392933


Do you guys have any idea what I am doing wrong?
Help really appreciated!

Answer

You need to also group by the user id to make the pivot query work as expected:

SELECT umeta.user_id,
       MAX(CASE WHEN umeta.meta_key = "lat" THEN umeta.meta_value ELSE 0 END) AS lat,
       MAX(CASE WHEN umeta.meta_key = "lng" THEN umeta.meta_value ELSE 0 END) AS lng
FROM wp_usermeta umeta
GROUP BY umeta.user_id

I included an explicit ELSE condition setting a 0 value, although you might not need this.

Comments