如何从WordPress获取所有特色图片？MySQL查询1242报错求解

Got it, let's tackle that 1242 - Subquery returns more than 1 row error first, then get you the product data with featured images you need.

What's Wrong With Your Original Query? #

Your original SQL mixes implicit joins (using commas) and explicit INNER JOINs without clear association rules, which creates a messy Cartesian product between tables. Specifically, you didn't link wp_posts p1 (your products) to wp_postmeta p3 (the table holding thumbnail IDs) properly—this causes the database to match every product with every thumbnail meta entry, leading to duplicate rows and the error you're seeing.

Corrected Query (For Products With Featured Images) #

This query properly links each table step-by-step to fetch only valid, related data:

SELECT 
    p1.ID AS product_id,
    p1.post_title AS product_title,
    p1.post_excerpt AS product_excerpt,
    p2.meta_value AS product_price,
    p1.guid AS product_permalink,
    p4.guid AS featured_image_url
FROM wp_posts p1
INNER JOIN wp_postmeta p2 
    ON p1.ID = p2.post_id 
    AND p2.meta_key = '_price'
INNER JOIN wp_postmeta p3 
    ON p1.ID = p3.post_id 
    AND p3.meta_key = '_thumbnail_id'
INNER JOIN wp_posts p4 
    ON p4.ID = p3.meta_value 
    AND p4.post_type = 'attachment'
WHERE 
    p1.post_type = 'product' 
    AND p1.post_status = 'publish';

Breakdown of the Fixed Query: #

• Starts with wp_posts p1 to grab only published products.
• Joins wp_postmeta p2 to pull the product price (using the _price meta key).
• Joins wp_postmeta p3 to get the featured image's attachment ID (from the _thumbnail_id meta key).
• Joins back to wp_posts p4 (as an attachment) to fetch the actual image URL from the guid field.

Include Products Without Featured Images #

If you want to keep products that don't have a featured image (they'll show NULL for the image URL), swap the INNER JOINs for LEFT JOINs on the thumbnail-related tables:

SELECT 
    p1.ID AS product_id,
    p1.post_title AS product_title,
    p1.post_excerpt AS product_excerpt,
    p2.meta_value AS product_price,
    p1.guid AS product_permalink,
    p4.guid AS featured_image_url
FROM wp_posts p1
INNER JOIN wp_postmeta p2 
    ON p1.ID = p2.post_id 
    AND p2.meta_key = '_price'
LEFT JOIN wp_postmeta p3 
    ON p1.ID = p3.post_id 
    AND p3.meta_key = '_thumbnail_id'
LEFT JOIN wp_posts p4 
    ON p4.ID = p3.meta_value 
    AND p4.post_type = 'attachment'
WHERE 
    p1.post_type = 'product' 
    AND p1.post_status = 'publish';

内容的提问来源于stack exchange，提问作者user379888