MySQL: Select several rows based on several keys on a given column

Question

I have this mysql table structure:

------------------------------------
| item_id | meta_key | meta_value |
------------------------------------
159         category    Bungalow
159         location    Lagos
159         price       45000

160         category    Bungalow
160         location    Abuja
160         price       53500
...
350         category    Bungalow
350         location    Lagos
350         price       32000

What I'd like to do is select several rows matching two or more criteria based on the meta_key column. For example, say I wanted to select the item_id for every 'Bungalow' located in 'Lagos'. How would I go about doing that?

Here's my attempt, which is not working:

SELECT `item_id` FROM `item_meta`
WHERE 
`meta_key` = 'category' AND `meta_value` = 'Bungalow'
AND 
`meta_key` = 'location' AND `meta_value` = 'Lagos'

Any help will be appreciated.

Abhik Chakraborty · Accepted Answer · 2015-06-22 10:32:15Z

1

If you are looking to find the records matching with both the criteria here is a way of doing it

select `item_id` FROM `item_meta`
where 
( `meta_key` = 'category' and `meta_value` = 'Bungalow' )
or
( `meta_key` = 'location' AND `meta_value` = 'Lagos' )
group by `item_id` having count(*)=2

answered Jun 22, 2015 at 10:32

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

3 Comments

Brave Soul Over a year ago

why having count(*)=2 ??

NaijaProgrammer Over a year ago

@Abhik, thanks for that. You understand what I need. Could you clarify for me the part of having count(*)=2? Any specific reason why the =2?

Abhik Chakraborty Over a year ago

Having count(*)=2 will ensure after group by that the returned item_id is from both the conditions. If you run the query without group by and having you can see it will return either 2 or 1 row for each matching item_id and we need only which are matching both hence group by and having.

AnkiiG · Accepted Answer · 2015-06-22 10:28:16Z

0

Try as below with brackets otherwise you will not get expected result :

SELECT `item_id` FROM `item_meta`
WHERE 
(`meta_key` = 'category' AND `meta_value` = 'Bungalow')
OR 
(`meta_key` = 'location' AND `meta_value` = 'Lagos')

answered Jun 22, 2015 at 10:28

AnkiiG

3,4861 gold badge19 silver badges28 bronze badges

1 Comment

NaijaProgrammer Over a year ago

see my comment on Ameya's answer below. The solution I'm looking for is NOT an EITHER...OR: category== 'Bungalow' || 'location' == 'Lagos'. I am looking for an inclusive solution: 'category' == Bungalow && location == Lagos.

h7r · Accepted Answer · 2015-06-22 10:46:00Z

0

One way is to independently select by the 2 properties you are interested in. By joining the table with itself you can find the entries (by item_id) that share the 2 parameters.

Something along the lines of the following:

select category.item_id 
from item_meta category 
    join item_meta location on location.item_id = category.item_id
where location.meta_key = 'location' AND location.meta_value = 'Lagos'
    and category.meta_key = 'category' AND category.meta_value = 'Bungalow'
group by category.item_id

answered Jun 22, 2015 at 10:46

h7r

5,0732 gold badges30 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL: Select several rows based on several keys on a given column

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related