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');
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: