Left Join前需剥离关联键后缀的SQL查询调整需求

Got it, let's fix this query so it properly matches SKUs by stripping off those custom suffixes from invoices.sku first. Here's the adjusted SQL that handles the suffix removal during the join:

SELECT productVariants.productKey
FROM [merchProductVariant] productVariants
LEFT JOIN [merchInvoiceItem] invoices 
  ON productVariants.sku = LEFT(invoices.sku, CHARINDEX('|', invoices.sku + '|') - 1)
WHERE invoices.sku IS NULL
GROUP BY productVariants.productKey

Key Changes Explained: #

• Suffix Stripping Logic: We use LEFT(invoices.sku, CHARINDEX('|', invoices.sku + '|') - 1) to extract the base SKU without the suffix:
  • CHARINDEX('|', invoices.sku + '|') finds the position of the first |. Adding a | to the end ensures even SKUs without a suffix return a valid position (so we don't get errors from CHARINDEX returning 0).
  • Subtracting 1 gives us the last character of the base SKU, and LEFT grabs everything up to that point.
• Preserved Original Logic: The rest of the query stays true to your original goal—left joining to find product variants with no matching invoice items, then grouping by productKey.

If you ever need to handle different suffix delimiters, just swap out the | in CHARINDEX with your new separator and the logic will still hold.

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