WordPress considers both a post and the post's featured image as two separate posts, which seems to make it impossible to create a SELECT query putting the post and post's image URL into the same row, like this:
post_id imageURL
100 [URL]
How do you merge two rows in a MySQL table which have no column with a common value? The value in one column is associated with the value in a different column.
meta_id post_id meta_key meta_value
100 250 _thumbnail_id 101
101 255 _wp_attached_file [URL]
...to get a result like this
post_id imageURL
100 [URL]
WordPress seems to have changed it database structure since this question was answered on this site.
I am trying to understand the principle in MySQL of taking the value from one row and showing it in another row, when "Group by" is not available.
Many hours later of watching videos, trial and error, reading answers here, this query gets me only half-way to solving it, but either the Post ID or the Image URL are always missing. I cannot merge two rows to unite the Post with its image URL.
SELECT * FROM `wp_postmeta`
INNER JOIN wp_posts on wp_posts.id = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = "_wp_attached_file"
ORDER BY `wp_postmeta`.`post_id` DESC
Please help. There may be a simple bit of syntax to achieve this, which I am missing. The query is coming in via the WP API, so using raw SQL queries, not the functions available inside WordPress.
You are trying to get the data based on the value of post_id and meta_value, so this can be achieved by using Self JOIN
Here is the sample working MySQL query
SELECT p.id AS post_id, pm2.meta_value AS URL
FROM `wp_posts` AS p
INNER JOIN `wp_postmeta` AS pm1 ON p.id = pm1.post_id
INNER JOIN `wp_postmeta` AS pm2 ON pm1.meta_value = pm2.post_id
AND pm2.meta_key = '_wp_attached_file'
AND pm1.meta_key = '_thumbnail_id'
ORDER BY p.id DESC;
Hope this helps!