Mysql Select on row values

Question

Im trying to create a plugin for wordpress and I encounter a table with a structure like this

enter image description here

Sample data

enter image description here

How should my MySQL Select query look like if I want to get

user_id  where City = 'CityName' and Gender = 'Male' //sample query

Something like

SELECT user_id,value,country FROM `wp_bp_xprofile_data` WHERE value = 'CityName' AND value = 'Male' GROUP BY user_id

OUTPUT should like

1 | nameofperson_1 | country_name
2 | nameofperson_2 | country_name
3 | nameofperson_3 | country_name
4 | nameofperson_4 | country_name

You question is a little unclear. Please provide more detail of how the output should look. — Namphibian
– Namphibian, Commented Dec 3, 2013 at 5:54
Which is which? Which are the column names and which are the rows? — Nadeem_MK
– Nadeem_MK, Commented Dec 3, 2013 at 5:56
Thanks for all the help guys..well all answers was right. but i need to choose one :) — Cindy93
– Cindy93, Commented Dec 3, 2013 at 6:39

Mosty Mostacho · Accepted Answer · 2013-12-03 06:11:53Z

1

In order to match the field_id with a particular value you'll have to match them in pairs (field_id AND value). Then you will have to count the amount of matches.

SELECT user_id FROM wp_bp_xprofile_data t
WHERE (field_id = 270 AND value = 'Gender') OR
      (field_id = 354 AND value = 'City')
GROUP BY user_id
HAVING COUNT(*) = 2

Fiddle here.

One thing that results from applying the Entity-Attribute-Value (EAV) model is that your tables are not normalized then you should make sure that you don't have more than one field_id for a given user_id with the same value.

A workaround for this would be to query:

HAVING COUNT(distinct field_id) = 2

But this won't solve the data issue, of course.

Edit:

Given your question update... in order to return the additional data probably putting the conditions in the having clause might be better:

SELECT
  user_id,
  max(case when field_id = 270 then value end) nameOfPerson,
  max(case when field_id = 354 then value end) cityName
FROM wp_bp_xprofile_data t
GROUP BY user_id
HAVING SUM(
  (field_id = 270 AND value = 'Gender') +
  (field_id = 354 AND value = 'City')
) = 2

Fiddle here.

edited Dec 3, 2013 at 6:11

answered Dec 3, 2013 at 6:01

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cindy93 Over a year ago

what does having sum do?

Mosty Mostacho Over a year ago

It sums the amount of conditions that you want. In this case there are 2 conditions so it makes sure that both of them are matched

Justin Kiang · Accepted Answer · 2013-12-03 06:06:04Z

1

Now that I see your question have a desired output, try the following:

SELECT t3.user_id, t3.value, t.value FROM
wp_bp_xprofile_data t 
LEFT JOIN wp_bp_xprofile_data t1 ON t1.user_id = t.user_id
LEFT JOIN wp_bp_xprofile_data t2 on t2.user_id = t.user_id
LEFT JOIN wp_bp_xprofile_data t3 on t3.user_id = t.user_id
WHERE t2.field_id = 270 AND t2.value="Male" AND
t1.field_id=354 AND t1.value="CityName" AND
t3.field_id=1 AND
t.field_id=73

edited Dec 3, 2013 at 6:06

answered Dec 3, 2013 at 5:59

Justin Kiang

1,2906 silver badges13 bronze badges

Comments

Jason Heo · Accepted Answer · 2013-12-03 07:06:25Z

1

Assuming wp_bp_xprofile_data is for table above, and value has real value rather than "Name", "Gender" etc.

could you try this?

SELECT t1.user_id, t1.value, t2.value
FROM wp_bp_xprofile_data t1
  INNER JOIN wp_bp_xprofile_data t2 ON t1.user_id = t2.user_id
WHERE t1.field_id = 270 AND t1.value = 'Mail'
  AND t2.field_id = 354 AND t2.value = 'CityName'

edited Dec 3, 2013 at 7:06

answered Dec 3, 2013 at 5:55

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Collectives™ on Stack Overflow

Mysql Select on row values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related