Aggregate multiple mysql table results to one

Question

I'm writing a wordpress site that has a petition in it, I need to show all the petitioners by name, country and position.

But the wordpress theme I use is set up to store the values for the petition users in multiple rows (it seems wordpress always does this with users).

each user is stored as a WordPress user with the role of "petitioner". I managed to get all the petitioners using this query

SELECT display_name
     , meta_value
     , meta_key 
  FROM wp_users 
  JOIN wp_usermeta 
    ON wp_users.ID = wp_usermeta.user_id 
  WHERE meta_key = "petition_user_country" 
     OR meta_key = "petition_user_state" 
    AND meta_value !="" 
  ORDER 
     BY ID DESC;

This query now returns (only with test data)

+--------------------------+------------+-----------------------+
| display_name             | meta_value | meta_key              |
+--------------------------+------------+-----------------------+
| Test3 Test34             | AF         | petition_user_country |
| Test3 Test34             | Tester     | petition_user_state   |
| Test Testnomsen          | NO         | petition_user_country |
| Test Testnomsen          | Student    | petition_user_state   |
| Brushan                  | NO         | petition_user_country |
| Kristian                 | Student    | petition_user_state   |
| Andreas Noe              | NO         | petition_user_country |
| Georg  Simone            | NO         | petition_user_country |
+--------------------------+------------+-----------------------+

The meta_value column holds both the position of the person and the country they are from. I need my HTML-table to show each person as one and display country, position and name.

Answer 1

You could use aggregation to get the data in single row for each entity and its different attributes

SELECT display_name,
       MAX(CASE WHEN meta_key = 'petition_user_country' THEN meta_value ELSE NULL END) country,
       MAX(CASE WHEN meta_key = 'petition_user_state'   THEN meta_value ELSE NULL END) state,
FROM wp_users 
JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
WHERE meta_key IN('petition_user_country','petition_user_state')
  AND meta_value !="" 
GROUP BY display_name
ORDER BY display_name ASC;

Answer 2

Join wp_usermeta twice and include the wanted metakey in the ON clause.

SELECT display_name
     , wpumc.meta_value country
     , wpums.meta_value state
  FROM wp_users 
  JOIN wp_usermeta wpumc
    ON wp_users.ID = wpumc.user_id 
       AND wpumc.metakey = 'petition_user_country'
  JOIN wp_usermeta wpums
    ON wp_users.ID = wpums.user_id 
       AND wpums.metakey = 'petition_user_state'
  WHERE wpumc.meta_value !=""  
    AND wpums.meta_value !=""     
  ORDER 
     BY ID DESC;