mysql - combine two sql queries -

Question

I've have two sql queries which I'm trying to combine

The first:

SELECT * FROM wp_posts
JOIN wp_postmeta on (post_id=ID)
WHERE  meta_key = "packageID" and  meta_value = 1 
ORDER BY post_date limit 50

Joins the wordpress wp_post table to the wp_postmeta and gets all the posts meeting with packageID = 1 (I think it might be an inelegant way of doing it but it works)

The second

SELECT * FROM wp_postmeta
JOIN wp_posts ON (meta_value=ID) 
WHERE post_id = 2110
AND meta_key = '_thumbnail_id'

again joins the wp_post table to the wp_postmeta table, so for the post with the id 2110 it successfully gets the thumbnail for that posts. NB 2110 is just an example of an id

In Wordpress a thumbnail is a kind of post. So in this example the text which constitutes post 2110 is a associated with post 2115 - the latter being the thumbnail

What I'm trying to do is get the list as in the first query but also get thumbnails associated with each post

I think I need two joins but I can't see how to do it (being an sql beginner)

NB this will be in a script outside Wordpress so I can't use Wordpress's built-in functions

Answer 1

You can try this one,if there are more than one thumbnails for the post you can get the list of thumbnails separated by comma

SELECT 
  *,
  (SELECT 
    GROUP_CONCAT(meta_value) 
  FROM
    wp_postmeta 
  WHERE post_id = wp.ID 
    AND wpm.meta_key = "_thumbnail_id") AS `thumbnails`
FROM
  wp_posts wp 
  JOIN wp_postmeta wpm 
    ON (wpm.post_id = wp.ID) 
WHERE wpm.meta_key = "packageID" 
  AND wpm.meta_value = 1 
ORDER BY wp.post_date 
LIMIT 50 

Note : GROUP_CONCAT has a limit to concat characters but you can increase this limit

To get only one thumbnail you can try this

SELECT 
  *,
  (SELECT 
    (meta_value) 
  FROM
    wp_postmeta 
  WHERE post_id = wp.ID 
    AND wpm.meta_key = "_thumbnail_id" LIMIT 1) 
FROM
  wp_posts wp 
  JOIN wp_postmeta wpm 
    ON (wpm.post_id = wp.ID) 
WHERE wpm.meta_key = "packageID" 
  AND wpm.meta_value = 1 
ORDER BY wp.post_date 
LIMIT 50