Nesting this two SQL queries with no luck

Question

I have to get some data from WordPress tables using SQL but i´m having no luck nesting this two queries. Thanks in advance!

Query 1: This query gets all data of wp_post filtered by language (WPML Plugin). Returns something like wp_posts table

SELECT * 
FROM wp_posts 
JOIN wp_icl_translations t ON wp_posts.ID = t.element_id 
  AND t.element_type = CONCAT('post_', wp_posts.post_type)  
WHERE wp_posts.post_type = 'post' 
AND wp_posts.post_status = 'publish' 
AND ( ( t.language_code = 'en' AND wp_posts.post_type = 'post' ) )

Query 2: This query gets title, post_name, date, content and featured image URL of wp_posts table.

SELECT title, post_name, date, content, CONCAT(LEFT(image, LENGTH(image) - LOCATE('.', REVERSE(image))),'-150x150.',SUBSTRING_INDEX(image, '.', -1)) AS image
        FROM (
            SELECT    
                p.post_title AS title, 
                p.post_status AS 'status', 
                p.post_date AS date,
                p.post_content AS content,
                p.post_name AS post_name,
                (SELECT `guid` 
                    FROM wp_posts 
                    WHERE id = m.meta_value) 
                    AS image
            FROM wp_posts p, wp_postmeta m
            WHERE p.post_type = 'post'
            AND p.post_status = 'publish'
            AND p.id = m.post_id
            AND m.meta_key = '_thumbnail_id'
            ORDER BY date DESC
            LIMIT 5
        )TT

Finally working! I leave the working query here just in case that anyone finds it useful!

SELECT title, post_name, date, content, CONCAT(LEFT(image, LENGTH(image) - LOCATE('.', REVERSE(image))),'-150x150.',SUBSTRING_INDEX(image, '.', -1)) AS image
        FROM (
            SELECT    
                p.post_title AS title, 
                p.post_status AS 'status', 
                p.post_date AS date,
                p.post_content AS content,
                p.post_name AS post_name,
                (SELECT `guid` 
                    FROM wp_posts 
                    WHERE id = m.meta_value) 
                    AS image
            FROM (
                SELECT * 
                FROM wp_posts 
                    JOIN wp_icl_translations t ON wp_posts.ID = t.element_id 
                    AND t.element_type = CONCAT('post_', wp_posts.post_type)  
                WHERE wp_posts.post_type = 'post' 
                    AND wp_posts.post_status = 'publish' 
                    AND ( ( t.language_code = 'en' AND wp_posts.post_type = 'post' ) )
            ) p, wp_postmeta m
            WHERE p.post_type = 'post'
                AND p.post_status = 'publish'
                AND p.id = m.post_id
                AND m.meta_key = '_thumbnail_id'
            ORDER BY date DESC
            LIMIT 4
        )TT

Answer 1

One way to do it is to get all the post id values you are interested in and pass those to a filter in your WHERE clause. Something like:

WHERE p.ID IN (
    SELECT wp_posts.ID 
    FROM wp_posts 
    JOIN wp_icl_translations t ON wp_posts.ID = t.element_id 
      AND t.element_type = CONCAT('post_', wp_posts.post_type)  
    WHERE wp_posts.post_type = 'post' 
    AND wp_posts.post_status = 'publish' 
    AND ( ( t.language_code = 'en' AND wp_posts.post_type = 'post' ) ))
    ORDER BY date DESC
    LIMIT 4
)

This way you return all of the data from your outer query, but only for the post ids in your sub-query. Is that along the lines of what you're looking for?

Also, you should specify a JOIN condition for the joins in FROM wp_posts p, wp_postmeta m otherwise you will get a cross join.