How can i search for same value of different row from one single query on MySQL

I have this table 'post_meta'

post_id name        value
87      my_keyname1 randval1
87      my_keyname2 randval2

I want to search for 2 different row and wants to return a single post_id. I know all the values of the 'name' and 'value' and i want to satisfy my query that i get the same post_id with 2 different 'name' and 'value' row.

Solutions

If you filter the table only for the records of interest and then group by post_id, your desired results are those groups that contain exactly two distinct records:

SELECT   post_id
FROM     post_meta
WHERE    (name = 'my_keyname1' AND value = 'randval1')
      OR (name = 'my_keyname2' AND value = 'randval2')
GROUP BY post_id
HAVING   COUNT(DISTINCT name, value) = 2

Should it be guaranteed that the name or value of each record within a group is unique, you can replace COUNT(DISTINCT name, value) with COUNT(*) for better performance.

SELECT DISTINCT post_id FROM post_meta
WHERE (name = 'my_keyname1' AND value = 'randval1')
OR (name = 'my_keyname2' AND value = 'randval2');
Tags: Mysql / Wordpress

Similar questions

Replace values in MySQL colums from row x to row y
I'd need to replace post dates from wordpress post table. There are >800.000 post entries with the same date because of a migration. How can I replace the date by "from row x to row"? For example: and so on... Or maybe replacing by post id? I know there is a sql query to do this, but I can not remember which one and how to use it correctly.
MySQL: Load row-based data into object with 1 MySQL query?
I would like to load a customer from WooCommerce (WordPress) into a object but WooCommerece uses rows to store both the data and the name / identifier of the data. From Joomla I am used to just selecting columns to get the information. So my SQL got at bit longer when working with WooCommerce because I had to use some PHP. It is working but can som...
Should a child theme share the same theme options row as the parent, or should it have it's own options row?
I'm developing a theme which uses the NHP Theme Options Framework to add some theme options. Naturally, the theme options framework creates a single row in the database to store the theme's options into. If I create a child theme of the parent, it shares the same theme options as the parent, which leads me to my question: Should a child theme share...
Update a row with data from another row in the same table based on a similar field
I am using wordpress and I want to copy data from a custom field to another custom field. Here is a visually representation of the table: I want to copy the meta_value of the shortcode to the meta_value of the video_url if their post_id matches This is what I have so far but I am getting syntax error: This is the error that I am getting:
mysql: Creating a Query using AND Operator between same row in a Table
Currently I am working on a Wordpress Project and i am stuck on this query creation for approx one week any help will be appreciated Below is a basic example what i am trying to do:- The Problem I am Facing is when i run this above query it does not bring any data for me This is what my current retirement is i am creating a filter function and this...
main menu in one row then sub menu in another horizontal row in wordpress
i am using word press. actually i want menu plugin for main menu menu in one row then the sub menus in another row. any one know which menu plugin is used for that scenario? Menu:

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.