MySQL — Error in your SQL syntax (WHERE IN UNION)

SELECT * 
FROM `SC_products` 
WHERE `productID` 
IN (
    (
        SELECT `productID` 
        FROM `SC_product_options_set` 
        LEFT JOIN `SC_products_opt_val_variants` ON `SC_product_options_set`.`variantID` = `SC_products_opt_val_variants`.`variantID` 
        WHERE `SC_products_opt_val_variants`.`optionID` = '14'
        GRO开发者_如何学JAVAUP BY `SC_product_options_set`.`productID` 
        ORDER BY `SC_products_opt_val_variants`.`sort_order` ASC 
    )
    UNION ALL
    (
        SELECT `productID` 
        FROM `SC_product_options_values` 
        WHERE `SC_product_options_values`.`optionID` = '14'
        ORDER BY `SC_product_options_values`.`option_value_ru` ASC 
    )
)
GROUP BY `SC_products`.`productID` 
LIMIT 0 , 30

ERROR №1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL ( SELECT productID FROM SC_product_options_values WHERE `SC_produ' at line 7

Please, help.

Try this:

SELECT * 
FROM   `sc_products` 
WHERE  `productid` IN (
                     SELECT `productid` 
                        FROM   `sc_product_options_set` 
                               LEFT JOIN `sc_products_opt_val_variants` 
                                 ON `sc_product_options_set`.`variantid` = 
                                    `sc_products_opt_val_variants`.`variantid` 
                        WHERE  `sc_products_opt_val_variants`.`optionid` = '14' 
                        GROUP  BY `sc_product_options_set`.`productid` 
                       UNION ALL 
                       SELECT `productid` 
                        FROM   `sc_product_options_values` 
                        WHERE  `sc_product_options_values`.`optionid` = '14' 
                                            ) 
GROUP  BY `sc_products`.`productid` 
LIMIT  0, 30  

P.S: You don't need an ORDER BY in your subquery becuase it is in a IN clause

IIRC, you have to put the ORDER clause outside of the queries you're UNIONing together. And if you do that, you'll need to make sure you select those columns in each of the to-be-UNIONed queries so you can order them once you've gotten your full recordset.

After that, you'll need to wrap those results in another SELECT layer so your IN comparison will work.