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

try with the following code

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

use proper alias and try it.

Answer 2

Try this

SELECT * FROM wp_posts P1
LEFT JOIN wp_postmeta M1 ON (M1.post_id=P1.ID)
WHERE  (M1.meta_key = "packageID" and  M1.meta_value = 1 )
LEFT JOIN wp_postmeta M2 ON (M2.meta_key=P1.ID AND M2.meta_key = '_thumbnail_id') 
LEFT JOIN wp_posts P2 ON (M2.meta_value=P2.ID)
ORDER BY P1.post_date limit 50

Answer 3

Try using the post_type column. The attachments have a post_type of 'attachment'. I can further explain if needed.

Also the post to which the thumbnail is attached to will be in the column post_parent.

Answer 4

global $wpdb;

    $query7 = "SELECT distinct wp_postmeta.meta_value, wp_postmeta.meta_key, wp_posts.ID
        FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
        INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
        WHERE wp_posts.ID = wp_postmeta.post_id
        AND wp_posts.post_status = 'publish'
        AND wp_postmeta.meta_key = 'packageID'
        AND wp_postmeta.meta_value = 1
        AND (mt1.meta_key LIKE '_thumbnail_id')
    $output = $wpdb->get_results( $query7 );

Use join with different aliases when joining same table more than once.

Hope this helps.

Answer 5

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