CODEWITHSUNDEEP
mysqlunion
user576126
user576126

Reputation:

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'
        GROUP 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.

Upvotes: 2

Views: 2201

Answers (2)

Chandu
Chandu

Reputation: 82943

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

Upvotes: 1

Kyle Humfeld
Kyle Humfeld

Reputation: 1907

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.

Upvotes: 0

Related Questions