2

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?

2

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;
  • That's right... I guess question isn't partially correct as "downloadale" and "virtual" will be in different tuple. They cannot be same at the same time!! Unless column accept both values! but this is what user wanted. – mysql_user Dec 17 '15 at 12:03
  • Arggg ok, I didn't realize it. Sorry, but I proceed to unaccept the answer. – miganml Dec 18 '15 at 11:23
  • If this works for you => correct the question. If this doesn't work for you, provide more details, what you're looking for? – mysql_user Dec 18 '15 at 11:26
  • Ok, question edited. I hope it's more clear now. – miganml Dec 18 '15 at 20:12
  • "values downloable and virtual are 'no' at the same time. " Because these are values belonging to same field they cannot be same(No) at the same time. But then I saw you have also mentioned post_id field as "100" for both!! So... Updated answer above.... – mysql_user Dec 19 '15 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.