Mysql select query where multiple row

Question

I want to select post_id where (meta_key = group_name and meta_value = Caucaia) and (meta_key = _featured and meta_value = 1)

Table: wp_postmeta

post_id | meta_key | meta_value
---------------------------
746     | group_name | Caucaia
746     | _featured  | 1
747     | group_name | Caucaia
747     | _featured  | 0
1791    | group_name | Aruba
1791    | _featured  | 1

I tried the below query but doesn't works

SELECT `post_id` FROM `wp_postmeta` WHERE (`meta_key` = 'group_name' and `meta_value` ='Caucaia' and `post_id` in (746, 747, 731, 1791, 1799)) or (`meta_key` = '_featured' and `meta_value` ='1' and `post_id` in (746, 747, 731, 1791, 1799)) GROUP BY `post_id`

How do I make query to select from multiple row? or is there any other way?

Gordon Linoff · Accepted Answer · 2015-10-02 18:40:35Z

1

Use aggregation:

SELECT `post_id`
FROM `wp_postmeta`
WHERE (`meta_key` = 'group_name' and `meta_value` ='Caucaia' and `post_id` in (746, 747, 731, 1791, 1799)) or
      (`meta_key` = '_featured' and `meta_value` ='1' and `post_id` in (746, 747, 731, 1791, 1799))
GROUP BY `post_id`
HAVING COUNT(*) = 2;

Actually, I notice the two lists are the same. I would write this using list constructors:

SELECT `post_id`
FROM `wp_postmeta`
WHERE (post_id` in (746, 747, 731, 1791, 1799)) AND
      (meta_key, meta_value) IN (('group_name', 'Caucaia'), ('_featured', '1'))
GROUP BY `post_id`
HAVING COUNT(*) = 2;

edited Oct 2, 2015 at 18:40

answered Oct 2, 2015 at 18:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Mureinik · Accepted Answer · 2015-10-02 18:40:46Z

0

Once way to do this is to have a where clause with all the combinations you need, and count the number of rows it returns:

SELECT   post_id
FROM     wp_postmeta
WHERE    (meta_key = 'group_name' AND meta_value = 'Caucaia') OR 
         (meta_key = '_featured' AND meta_value = '1')
GROUP BY post_id
HAVING   COUNT(*) = 2

answered Oct 2, 2015 at 18:40

Mureinik

316k54 gold badges399 silver badges405 bronze badges

1 Comment

CodeGodie Over a year ago

did you forget the IN section?

Collectives™ on Stack Overflow

Mysql select query where multiple row

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related