mysql - select grouped values

Question

I have this result set

https://i.sstatic.net/7qb5g.jpg

and i'm trying to get entries as follow

get the ad_id of entries that have an attribute_id = 5 and value = 'Yamaha Mate V50' and attribute_id = 6 and value = 2012

i've tried stuff like

SELECT ad_id FROM value WHERE attribute_id = 5 && value = 'Yamaha Mate V50' && attribute_id = 6 && value = '2012'

this gives an empty result set

and if i did this

SELECT ad_id FROM value WHERE attribute_id = 5 && value = 'Yamaha Mate V50' || attribute_id = 6 && value = '2012'

i get ad_id = 1 and 3 which is good however i also get ad_id = 2 which is logical because it does have this condition (attribute_id = 6 && value = '2012')

so what should i do to select only the ad_ids that fulfill both conditions

(attribute_id = 5 && value = 'Yamaha Mate V50') "AND" (attribute_id = 6 && value = '2012')

Thanks in advance

Michael Krelin - hacker · Accepted Answer · 2012-11-05 08:07:22Z

2

SELECT ad_id FROM value
WHERE (attribute_id,value) IN ((5,'Yamaha Mate V50'),(6,'2012'))
GROUP BY ad_id
HAVING count(*)=2

edited Nov 5, 2012 at 8:07

answered Nov 4, 2012 at 19:33

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

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

2 Comments

erratbi Over a year ago

hellow @Michael Krelin - hacker your answer was good but i've faced another dead end the thing is i want not to extract entries between 2 values. it's kinda like this WHERE (attribute_id,value) IN ((5,'Yamaha Mate V50'),(6,'2012'), (7, between 200 and 600))

Michael Krelin - hacker Over a year ago

I'm not sure what you're talking about. If you want to add another pair of values, just make it 3 in count comparison.

Collectives™ on Stack Overflow

mysql - select grouped values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related