含两个子查询的SELECT语句返回重复结果，如何修复？

Hey Steveo, let's break down why your current query is spitting out duplicates and get it sorted properly.

The Root of the Duplicate Issue #

Your original query uses two uncorrelated subqueries that pull all product IDs and variation IDs for the entire order (3989). When an order has multiple line items, each subquery returns multiple rows. The database then combines these rows in a Cartesian product—meaning every product ID gets paired with every variation ID from the order—which creates those unwanted duplicates.

The Better Approach: Use Joins Instead of Subqueries #

Instead of nested subqueries, we can directly join the order items table with the meta table twice (once for product IDs, once for variation IDs). This gives you one row per order item, with no duplicates. Here's the corrected query:

SELECT 
    im_product.meta_value AS product_id,
    im_variation.meta_value AS variation_id,
    oi.order_item_id, -- Optional: include to see which line item each ID belongs to
    oi.order_id
FROM ctc_woocommerce_order_items oi
LEFT JOIN ctc_woocommerce_order_itemmeta im_product 
    ON oi.order_item_id = im_product.order_item_id 
    AND im_product.meta_key = '_product_id'
LEFT JOIN ctc_woocommerce_order_itemmeta im_variation 
    ON oi.order_item_id = im_variation.order_item_id 
    AND im_variation.meta_key = '_variation_id'
WHERE oi.order_id = 3989;

Why This Works #

• We start with the ctc_woocommerce_order_items table (aliased as oi), which has one row per line item in the order.
• We join to ctc_woocommerce_order_itemmeta twice: once to grab the _product_id meta value, once for _variation_id.
• By adding the meta_key condition directly in the ON clause (instead of WHERE), we ensure we only pull the relevant meta fields for each line item.
• This structure guarantees one row per order item, so no duplicates—you'll get exactly the product and variation ID for each item in the order.

If You Insist on Using Subqueries (Not Recommended Here) #

If for some reason you need to stick with subqueries, you'd have to ensure each subquery returns only a single value per row. But this only makes sense if you're targeting a specific line item, not all items in the order. For example:

SELECT 
    (SELECT meta_value FROM ctc_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = '_product_id') AS product_id,
    (SELECT meta_value FROM ctc_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = '_variation_id') AS variation_id
FROM ctc_woocommerce_order_items oi
WHERE oi.order_id = 3989;

This uses correlated subqueries that tie back to each individual order item, so you still get one row per item without duplicates. But the join approach is cleaner and more efficient for this use case.

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