Combine 2 Queries with different number of rows

1 Views

I have a query that connects to a wordpress db and returns some posts.

In order to get the image for each of these posts i run another query inside a php foreach loop

Read More

The problem is that running the second query inside the foreach loop is extremely slow and i need another way to merge these 2 queries into 1.

The first query 

SELECT pm. * , p.*
FROM wp_posts p
JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE pm.meta_key = 'accommodation_location_post_id'
AND pm.meta_value
IN (   
SELECT pi.ID
FROM wp_posts pi
WHERE pi.post_title LIKE '%Cyprus%')

Based on the returned post ids, i need for each of these ids
the featured image.

This query does this job, but only for 1 id only.

The second query 

SELECT wp_posts.guid 
FROM wp_posts
WHERE wp_posts.ID = 
(Select wp_postmeta.meta_value 
FROM wp_postmeta 
WHERE wp_postmeta.meta_key = 
'_thumbnail_id' AND wp_postmeta.post_id = 'The id of each post')

I need a query to return all the posts alongside with its images.

Post Views: 1

Related posts

1 comment

  1. I really don’t understand your data structure, but try this query

    select
    t1.*, t2.guid
from (
    SELECT pm. * , p.*
    FROM wp_posts p
    JOIN wp_postmeta pm ON pm.post_id = p.ID
    WHERE pm.meta_key = 'accommodation_location_post_id'
AND pm.meta_value IN (
    SELECT pi.ID
        FROM wp_posts pi
        WHERE pi.post_title LIKE '%Cyprus%'
    )
) t1
left join (
    select
        p2.guid, pm2.post_id
    from
        wp_posts p2
        join wp_postmeta pm2 on
            pm2.meta_value = p2.ID
            and pm2.meta_key = '_thumbnail_id'
) t2 on t2.post_id = t1.ID

Comments are closed.