Mysql – Join two SELECT results with the same post_id value

MySQLselectWordpress

I need to do a query to the MySQL database of my site built in WordPress / WooCommerce.

The 'wp_posts' table keeps products, articles and more. Every row in 'wp_posts' has a 'post_id' to connect 'wp_posts' with 'wp_postmeta' table. In the 'wp_postmeta' table, there are rows with additional data and values for every 'wp_post'. These rows have 'meta_key', 'meta_value' and 'post_id' values.

I need to SELECT post_ids where values downloable and virtual are 'no' at the same time. To have an example, there are two rows in 'wp_postmeta', one where 'meta_key' is '_downloable', 'meta_value' is 'no' and 'post_id' is 100, and another one where 'meta_key' is '_virtual', 'meta_value' is 'no' and 'post_id' is 100.

I just get to do it separately, as follows:

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key='_downloadable'
AND wp_postmeta.meta_value='no';

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key='_virtual'
AND wp_postmeta.meta_value='no';

Can you help me?

Best Answer

Are you looking for IN?

SELECT wp_postmeta.post_id
FROM wp_postmeta
WHERE wp_postmeta.meta_key in ('_downloadable', '_virtual')
AND wp_postmeta.meta_value='no';

Updated answer as per updated question:

I'd try to understand your question.

You have following table:

mysql> select * from s;
+------+------+------+
| id   | skey | sval |
+------+------+------+
|    1 | v    | n    |
|    1 | d    | n    |
|    2 | b    | n    |
|    3 | v    | n    |
|    3 | d    | y    |
|    4 | v    | n    |
|    4 | d    | n    |
|    2 | c    | n    |
+------+------+------+

You want final answer as 1 and 4 because both "v" and "d" are "n" at-the-same-time (for id).

If this is right requirement then below query should work.

select id from s where skey in ('v','d') and sval='n' group by id having count(*)=2;
Related Question