MySQL多表关联查询：如何导出SKU与URL并按条件设置WHERE子句

Got it, let's break this down based on your requirements and the tables you're working with. First, let's align on what each table does here:

• catalog_product_entity (aliased as cpe): Holds core product data, including the sku we need and entity_id (unique product identifier)
• catalog_product_relation (aliased as cpr): Manages parent-child product links (like configurable products and their simple variants)
• catalog_product_entity_varchar (aliased as cpev): Stores text-based product attributes — this is where your URL value lives (usually tied to an attribute like url_key, so we'll need to target the right attribute_id for this)

Your core rule is: If a product is a child in the relation table (matches child_id), use its parent's ID for filtering; otherwise, use the product's own entity_id. Here's the complete query that implements this logic:

SELECT 
    cpe.sku,
    cpev.`value` AS product_url
FROM 
    catalog_product_entity cpe
LEFT JOIN 
    catalog_product_relation cpr 
    ON cpe.entity_id = cpr.child_id
LEFT JOIN 
    catalog_product_entity_varchar cpev 
    -- Use parent_id if it exists, else fall back to the product's own entity_id
    ON COALESCE(cpr.parent_id, cpe.entity_id) = cpev.entity_id
    -- Replace YOUR_URL_ATTRIBUTE_ID with the actual ID for your URL attribute (e.g., url_key)
    AND cpev.attribute_id = YOUR_URL_ATTRIBUTE_ID
    -- Optional: Uncomment and set if you need to target a specific store view
    -- AND cpev.store_id = 0
WHERE 
    -- Apply your filter using the resolved product ID (parent or self)
    COALESCE(cpr.parent_id, cpe.entity_id) = YOUR_TARGET_PRODUCT_ID;

Key Details to Note: #

• LEFT JOIN catalog_product_relation cpr: This checks if the product is a child in any parent-child relationship. If it is, we get the parent_id; if not, that field will be NULL.
• COALESCE(cpr.parent_id, cpe.entity_id): This is the magic that handles your requirement — it picks the parent ID when available, otherwise uses the product's own ID for both joining to the URL table and applying filters.
• Don't forget to replace YOUR_URL_ATTRIBUTE_ID with the actual ID of the attribute storing your URLs. You can find this in the eav_attribute table by looking for the attribute code (like url_key).
• If you need to filter multiple products, swap the = in the WHERE clause with IN, e.g., COALESCE(cpr.parent_id, cpe.entity_id) IN (123, 456, 789).

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