MySQL query multiple AND

Question

Supposing I have a MySQL table (meta_table) as follows with the following data:

meta_id,post_id,meta_key,meta_value
5,10,my_key1,value1
10,11,my_key2,value2
15,12,my_key3,value3
35,11,my_key4,value4

How can I get the post_id if meta_value is value2 and value4 (the answer should be 11). I have tried this:

SELECT post_id from meta_table where meta_value='value2' and meta_value='value4'

But it does not return 11 as the post_id. Any suggestions?

Both Mahmoud's and Petah's answers should work. Can you have two different rows with post_id=11 and meta_value=value2? If yes then you might have to modify the answers slightly. — mtariq
– mtariq, Commented Dec 3, 2012 at 9:16

Petah · Accepted Answer · 2012-12-03 09:08:46Z

2

With a join:

SELECT a.post_id
FROM meta_table AS a
JOIN meta_table AS b ON a.post_id = b.post_id
WHERE a.meta_value = 'value2' AND b.meta_value = 'value4'

http://www.sqlfiddle.com/#!2/96d2b/4/0

answered Dec 3, 2012 at 9:08

Petah

46.2k30 gold badges161 silver badges216 bronze badges

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

Comments

Mahmoud Gamal · Accepted Answer · 2012-12-03 09:07:20Z

2

SELECT post_id
from meta_table 
where meta_value IN ('value2', 'value4')
GROUP BY post_id
HAVING COUNT(*) = 2;

SQL Fiddle Demo

answered Dec 3, 2012 at 9:07

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

2 Comments

Petah Over a year ago

Good answer, but is it better than a join?

Petah Over a year ago

You can use a JOIN, as per my answer. The reason to is because HAVING is a post filter condition, when using a JOIN + WHERE is pre-filtered and makes better use of indexes.

Muthu Kumaran · Accepted Answer · 2012-12-03 09:07:04Z

1

Try using OR

SELECT post_id from meta_table where meta_value='value2' OR meta_value='value4'

Or you can use IN

SELECT post_id from meta_table where meta_value IN ('value2', 'value4')

answered Dec 3, 2012 at 9:07

Muthu Kumaran

18k5 gold badges50 silver badges72 bronze badges

2 Comments

Mahmoud Gamal Over a year ago

This will get the post_id that has value2 or value4. But I think the OP is looking for the post_ids that had both value2 and value4.

Muthu Kumaran Over a year ago

@MahmoudGamal Maybe. By the way I like your answer (+1)

Yohanes Gultom · Accepted Answer · 2012-12-03 09:06:59Z

0

It should be :

SELECT post_id from meta_table where meta_value='value2' or meta_value='value4'

answered Dec 3, 2012 at 9:06

Yohanes Gultom

3,8522 gold badges27 silver badges39 bronze badges

Comments

Thas · Accepted Answer · 2012-12-03 09:08:47Z

0

query is : -

SELECT post_id from meta_table
where meta_value='value2' or meta_value='value4'
GROUP BY post_id

answered Dec 3, 2012 at 9:08

Thas

111 silver badge5 bronze badges

Collectives™ on Stack Overflow

MySQL query multiple AND

5 Answers 5

Comments

SQL Fiddle Demo

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related