How to merge two SQL queries to get latest Wordpress Post and Featured Image

Question

I'm trying to show latest Post Excerpt, Post Title and Featured Image on an ASP page. To simplify the query I add the Permalink as a custom field for each Post. I have a query that gets all except Featured Image and I have another query that gets Featured image but I can't work out how to merge them into one query.

// Gets Post Excerpt and Post Title

SELECT
 wp_posts.id, 
 wp_posts.post_title, 
 wp_postmeta.meta_value, 
 wp_postmeta.meta_key, 
 wp_posts.post_excerpt 
 FROM 
 wp_postmeta 
 INNER JOIN wp_posts p ON (wp_postmeta.post_id = wp_posts.ID) 
 WHERE post_id IN (
 SELECT wp_posts.id 
 FROM wp_posts 
 WHERE post_type = 'post' 
 AND post_status = 'publish' 
 AND meta_key = 'my_permalink' 
 ORDER BY post_date, wp_posts.id
 ) 
 ORDER BY wp_posts.post_date DESC, wp_postmeta.post_id 
 LIMIT 2

// Gets Featured Images for a Post

 SELECT p.*
      FROM wp_postmeta AS pm
     INNER JOIN wp_posts AS p ON pm.meta_value=p.ID 
     WHERE pm.post_id = $ID
       AND pm.meta_key = '_thumbnail_id' 
     ORDER BY p.post_date DESC 

Can anyone help me merge these queries? Thanks.

Sample data to be returned: ID | post_title | post_excerpt | meta_value_my_permalink | featured_image_guid

** UPDATE * I've managed to get the following which works fine except I can't get more that one row as I get an error when I try and use 'IN' in a subquery e.g. pm2.post_id IN (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 2)

SELECT 
  p.post_title,
  p.post_excerpt,
  pm.meta_value AS permalink,
  p2.guid as thumbnail,
  p2.post_title as image_alt
FROM
  wp_postmeta pm
  INNER JOIN wp_posts p ON (pm.post_id = p.ID),
  wp_postmeta pm2
  INNER JOIN wp_posts p2 ON (pm2.meta_value = p2.ID)
WHERE
  pm.post_id = (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1) AND 
  pm.meta_key = 'my_permalink' AND 
  pm2.post_id = (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1) AND 
  pm2.meta_key = '_thumbnail_id'

Answer 1

It is difficult to see what you are trying to get without some sample data but I think that you know the post ID and need the info from both the my_permakink and _thumbnail_id keys? Since I'm on a tablet I will give you the bare bones only and let you limit the result set and filter it.

Select *
From wp_posts p
     Inner join
     Wp_postmeta pm1 on p.id = pm1.post_id and metakey = 'my_permalink'
     Inner join
     Wp_postmeta pm2 on p.id = pm1.post_id and metakey = '_thumbnail_id'

This will give you every post that has an image with the info for both metakeys. If you want the info for posts without an image change the second inner join to a left join.

Answer 2

A bunch of joins and a view as the subquery has given me what I need.

SELECT 
  p.post_title,
  p.post_excerpt,
  pm.meta_value AS permalink,
  p2.guid AS thumbnail,
  p2.post_title AS thumbnail_alt
FROM
  wp_postmeta pm
  INNER JOIN wp_posts p ON (pm.post_id = p.ID)
  INNER JOIN wp_postmeta pm2 ON (pm2.post_id = p.ID)
  INNER JOIN wp_posts p2 ON (pm2.meta_value = p2.ID)
WHERE
  pm.meta_key = 'my_permalink' AND
  pm2.meta_key = '_thumbnail_id' AND 
  p.ID IN (SELECT * FROM vwLatestPostIds)

Answer 3

Use this to retrieve the post title, post id, post featured thumbnail url, post category etc - "SELECT a.ID id, a.post_title title, a.post_content content,taxonomy, name, a.post_date_gmt postdate,max(c.guid) img_url FROM kb_posts a JOIN kb_term_relationships tr ON (a.id = tr.object_id) JOIN kb_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy='category') JOIN kb_terms t ON (t.term_id = tt.term_id AND t.term_id = $user_id)

            LEFT JOIN
            (select post_parent, max(post_date_gmt) as latest_image_date from kb_posts
            where post_type='attachment' GROUP BY post_parent) b 
            on a.id=b.post_parent

            LEFT JOIN kb_posts c
            on c.post_parent=a.id 
            and c.post_type='attachment' 
            and b.latest_image_date = c.post_date_gmt where c.guid IS NOT NULL


            GROUP BY a.ID ORDER BY a.ID DESC LIMIT 5"