I'm trying to classify my SKU's and I'm almost there. The desired outcome is:

I do get sample, cask, and project-cask right BUT I don't get project right (instead I get an incorrect cask).

The query I'm currently using is:

SELECT 
     pa.product_id
    ,pm.sku
    ,(CASE 
        WHEN pm.sku LIKE 'CSs%-%' THEN 'sample'

        WHEN pm.sku LIKE 'CS%/A'
          OR pm.sku LIKE 'CS%/B'
          OR pm.sku LIKE 'CS%/C'
          OR pm.sku LIKE 'CS%/D'
          OR pm.sku LIKE 'CS%/E' THEN 'project-cask'

        WHEN ( SELECT COUNT(sku) FROM wp_wc_product_meta_lookup 
                    WHERE sku LIKE pm.sku   '%' ) > 1 THEN 'project'

        WHEN pm.sku LIKE 'CS%' THEN 'cask'
    END) AS sku_class
FROM wp_wc_product_attributes_lookup AS pa
LEFT JOIN wp_wc_product_meta_lookup AS pm ON pa.product_id = pm.product_id
GROUP BY pa.product_id;

But my WHEN ( SELECT COUNT(sku) FROM wp_wc_product_meta_lookup WHERE sku LIKE pm.sku '%' ) > 1 THEN 'project' is failing and results in (MariaDB) errors like:

• Warning: #1292 Truncated incorrect DOUBLE value: 'CS2202'
• Warning: #1292 Truncated incorrect DOUBLE value: '%'

When I hardcode 'CS2202%' in the WHERE statement I see -logically- all casks as projects as CS2202 is indeed a project. So the issue is with the LIKE pm.sku '%' part.

How do I get that part working with the SKU from the main query?

P.S. The logic to tag a SKU as a project is when CSxxxx% occurs more than 1 time otherwise it's a single cask.

CodePudding user response：

Indeed, pm.sku '%' is the problem. Use CONCAT like this:

WHERE sku LIKE CONCAT(pm.sku, '%')

it's not used for concatenation in MySQL