Not tested 100% but this might work:
SELECT a.post_title, m1.meta_value AS SKU, m2.meta_value AS PRICE
FROM wp_posts a
LEFT JOIN wp_postmeta m1
ON a.ID = m1.post_id
LEFT JOIN wp_postmeta m2
ON a.ID = m2.post_id
WHERE a.post_type="product"
AND m1.meta_key='_sku'
AND m2.meta_key='_price'
Note that _price
field corresponds to the “sale price” (if existing) so you may grab also the “list price”:
SELECT a.post_title, m1.meta_value AS SKU, m2.meta_value AS SALE_PRICE, m3.meta_value AS LIST_PRICE
FROM wp_posts a
LEFT JOIN wp_postmeta m1
ON a.ID = m1.post_id
LEFT JOIN wp_postmeta m2
ON a.ID = m2.post_id
LEFT JOIN wp_postmeta m3
ON a.ID = m3.post_id
WHERE a.post_type="product"
AND m1.meta_key='_sku'
AND m2.meta_key='_price'
AND m3.meta_key='_regular_price'
Related Posts:
- simple sql query on wp_postmeta very slow
- How many WordPress SQL Queries per page?
- Multipart/formatted MySQL query problem
- Are database queries created using WordPress filters protected from SQL injection?
- Grouping related postmeta data via SQL query
- Mysql / WordPress killing my server with 80k users [closed]
- Slow meta_query with about 4 milion record on wp_postmeta
- Select User by Joining Multiple Meta Value Results
- mySQL query. ORDER BY meta_key
- Update slug (URL) of pending posts via phpMyAdmin
- My SQL function to change user_name
- You have an error in your SQL syntax – Help with query
- MySQL variable in query
- how would I create a custom query to get all users, and a related post based on a postmeta field?
- Help with MySQL to $WPDB query
- SQL query to select posts from multiple categories
- How do I see the mysql query generated by get_posts( $args )?
- Alter query with posts_clauses to retrieve NULL values last
- MySql Query very slow
- Searching With Apostrophe
- WordPress Query wp-terms SLOW
- wpdb COALESCE won’t work
- Get posts from category with custom query
- how to insert missing tags into the posts through mySQL?
- Ideas how to search & replace post_content when string contains a newline?
- Valid SQL query return empty
- mysql query – how to escape apostrophe?
- Query the WordPress database to get data together with replaced information
- Optimize slow SQL query for multiple meta values
- Updating with $qpdb->query() always returns 0 rows affected
- How to return count of custom post type posts with a specific custom field value via $wpdb?
- WordPress Mysql query and Duplicate
- Help with Related Posts Function
- How to delete all images from code in all post_content
- I want to get on those users their meta value are like “AGENT” .. but this query is not working
- Displaying data from custom table
- PHP Fatal error: Uncaught Error: Call to a member function insert() on null
- SQL query to delete users with multiple meta keys and comments
- OR condition not working
- Trouble migrating custom post types from non-wordpress cms
- Are there any best practices for creating a Like/Favourite feature in WordPress using custom MySQL tables and without any plugins?
- Performance of wp_get_attachment_image_srcset() and wp_get_attachment_image_url()
- How to make MySQL search queries with quotes
- Custom MySQL Query for Post and Post Meta
- Mysql query and odd results
- sql for querying post and their category
- Access Tables with number prefix
- Get all image from single page using this query
- Add multiple value to a query variable in WordPress
- Search and replace text across all posts
- wp_dropdown_categories with multiple select
- How to tune search argument in WP_Query to show only exactly the same results?
- How to get my loop to pull posts into three columns
- Custom $wpdb Query for Custom Post Type by Category
- Can’t pass table to $wpdb->prepare
- Date query year and month OR just year
- get post id using the $query_vars variable
- Calculating Bayesian average for custom post type
- Disallow categories from this MySQL query
- How to check current user in meta value array in WP_Query meta_query
- How select a specific query when setting offset?
- Change media attachment author via mysql query
- sql select query in wordpress ‘page’ [closed]
- wpdb->term_taxonomy count posts from both published and private posts?
- How to get related tags based on a category?
- Group custom post listing in admin section
- Using $wpdb (WPDB class) ‘replace’ with multiple WHERE criteria problem
- Attempt to improve WP search, can someone check my SQL query?
- WordPress Query orderby title with UTF8 string in title
- Query multiple tables at once?
- List author’s posts with SQL
- get_posts showing only 5 posts. (nopaging and posts_per_page -1 not working)
- Custom WordPress Table wpdb
- Order by title – but now built in wordpress func, sort the_title
- Display posts between two particular dates
- Order posts by date, but also give priority for a specific term
- $post->ID not working in combination with a custom query
- Update query for wp_posts and wp_postmeta
- Combine “Exclude” Query in functions.php
- prepare function sql safe method
- Wrap/close ‘query’ for a first-letter ordered listing of posts
- wpdb query with dynamic column name?
- How do you display the number of a post in a query?
- How can I get the name of term post meta value which equals term id
- Trying to use WP_Query to return post with and without meta values
- Return records between two meta datetimes saved as strings
- get products by custom date variable
- How to sort by two meta keys in admin area?
- how to get postmeta info based on multiple categories?
- Use $wpdb->get_results with filter based on array
- Add Hook for clearing transient when post is added
- Show links to child pages on both parent AND child pages
- Modify Global Posts Plugin
- How to reference same column name but different table in custom db query
- Retrieving custom field array value through db query
- Query Concatenation
- SQL Query : how copy all tags of post into their post content in wordpress by sql query
- How do I troubleshoot Maximum execution time of 60 seconds exceeded in updating a Media File?
- update $wpdb one query
- How to use a dynamic term id for the query block