含子查询的关联表用例：SQL动态JOIN查询报错求助排查

Hey there! Let's figure out why your SQL query is throwing errors and fix it up.

问题根源 #

Your core issue is a syntax mistake: CASE WHEN is an expression (it returns a single value), not a control flow statement for structuring your JOIN logic. You can't nest a JOIN inside a CASE branch—SQL doesn't allow that. That's exactly why your query is failing.

修正方案1：LEFT JOIN + CASE 取值 #

If you need to keep all records from products_features (even those without a matching value in the target table), this approach works best. We'll join all possible value tables first, then use CASE to pick the right value based on value_table:

SELECT 
    pf.*,
    CASE 
        WHEN pf.value_table = 'features_values_float' THEN fvf.value
        WHEN pf.value_table = 'features_values_string' THEN fvs.value
        -- Add more cases for other value tables here
        ELSE NULL
    END AS feature_value
FROM products_features pf
LEFT JOIN features_values_float fvf 
    ON pf.features_value_id = fvf.id
LEFT JOIN features_values_string fvs 
    ON pf.features_value_id = fvs.id
-- Add LEFT JOINs for other value tables as needed

修正方案2：UNION ALL 拆分查询 #

If you only want records that have a matching value in the corresponding table, UNION ALL is a more efficient option (especially with large datasets). We'll split the query into separate parts for each value table, then combine the results:

-- Get float values
SELECT pf.*, fvf.value AS feature_value
FROM products_features pf
JOIN features_values_float fvf 
    ON pf.features_value_id = fvf.id
WHERE pf.value_table = 'features_values_float'

UNION ALL

-- Get string values
SELECT pf.*, fvs.value AS feature_value
FROM products_features pf
JOIN features_values_string fvs 
    ON pf.features_value_id = fvs.id
WHERE pf.value_table = 'features_values_string'

-- Add more UNION ALL blocks for other value tables

小提示 #

• Make sure the value columns in each value table are compatible (or cast them to the same type if needed) so the UNION ALL or CASE result is consistent.
• If features_value_id is unique across all value tables, both approaches will work smoothly.

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