CODEWITHSUNDEEP
mysqlsql
BigUncleE
BigUncleE

Reputation: 51

Write mySql command to get meta data based on gender?

I read through a previous question "Right mySql command to get meta information". This was exactly what I was looking for and the response from Andriy M was the answer to most of my question. The issue I am having is adding WHERE statement that would allow me to only list Male users or Female users in the query. I am pulling two meta_key values (gender2 and play_county), if I use a WHERE statement the other meta_key value becomes NULL. I would like help getting both meta_key values.

wp_users table

ID|user_login| user_email |user_pass|date_registered
==================================================
 1|   me     |[email protected]  |f239j283r|   2011-01-01
 2|   her    |[email protected]|g234j342q|   2011-02-02

wp_usermeta table

umeta_id|user_id|meta_key       |meta_value
==========================================
   1    |   1   | play_county   | Gwinnett
   2    |   1   | gender2       | Male
   1    |   2   | play_county   | Cobb
   2    |   2   | gender2       | Female

Here is the code that gives me most of what I would like:

SELECT  u.id, u.user_email, u.display_name,
  MIN(CASE m.meta_key WHEN 'play_county' THEN m.meta_value END) AS County,
  MIN(CASE m.meta_key WHEN 'gender2' THEN m.meta_value END) AS Gender
 FROM wp_users u
  LEFT JOIN wp_usermeta m ON u.ID = m.user_id
    AND m.meta_key IN ('play_county', 'gender2')
GROUP BY u.ID

Here is the output:

ID | user_email  | display_name | County   | Gender
====================================================
1  | [email protected]  | Me Male      | Gwinnett | Male
2  | [email protected]| She Female   | Cobb     | Female

When I add the Where statement:

WHERE  m.meta_key = 'gender2' AND m.meta_value = 'Female'

I get this output:

ID | user_email  | display_name | County   | Gender
====================================================
2  | [email protected]| She Female   | NULL     | Female

How can I get the 'play_county' value to be the correct value?

Upvotes: 1

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270311

Use a separate JOIN for each key type:

SELECT u.id, u.user_email, u.display_name,
       mc.meta_value AS County,
       mg.meta_value AS Gender
FROM wp_users u JOIN
     wp_usermeta mc
     ON u.ID = mc.user_id AND
        mc.meta_key = 'play_county' JOIN
     wp_usermeta mg
     ON u.ID = mg.user_id AND
        mg.meta_key = 'gender2' AND
        mg.meta_value = 'Female';

Separate joins are fine for a handful of different keys. If you have a lot of keys that you want returned, then the conditional aggregation approach works -- but it is a bit more cumbersome for the type of filtering that you want to do.

Upvotes: 2

Related Questions