Query:
SELECT
products.product_id
,products.product_name
,products.base_sell_price
,products.base_list_price
,products.base_price_mode
,products.product_prefix
,products.product_root
,products.product_suffix
,products.product_status
,products.main_demand_range_id
,ranges.range_name AS collection_name
,ranges.range_prefix AS collection_prefix
,ranges.range_root AS collection_root
,ranges.range_suffix AS collection_suffix
,ranges.collection_title
,ranges.collection_subtitle
,ranges.collection_num_products
,ranges.collection_num_colours
,ranges.collection_swatch_dots
,ranges.collection_feature_1
,ranges.collection_feature_2
,demand_range_content.flat_url AS collection_link
,demand_range_content.visibility AS collection_visibility
-- hero image (products.image_id)
,assets.asset_name AS asset_name
,assets.asset_id AS asset_id
-- lifestyle image (fallback to hero)
,COALESCE(a_lifestyle.asset_name, assets.asset_name) AS lifestyle_asset_name
,COALESCE(a_lifestyle.asset_id, assets.asset_id) AS lifestyle_asset_id
-- deepetch image (fallback to hero)
,COALESCE(a_deepetch.asset_name, assets.asset_name) AS deepetch_asset_name
,COALESCE(a_deepetch.asset_id, assets.asset_id) AS deepetch_asset_id
,products.product_message
,content.content_id
,content.flat_url
,products.stock_message_id
,products.default_variant_id
,products.colours_json
,product_range.range_ids
,products.revenue
,vi.file_name AS variant_file_name
FROM
products
LEFT JOIN content
ON products.product_id=content.product_id
AND content.site_id='6'
AND content.visibility='1'
AND content.content_type='2'
-- hero join chain
LEFT JOIN images
ON products.image_id=images.image_id
LEFT JOIN assets
ON images.asset_id=assets.asset_id
-- lifestyle join chain
LEFT JOIN images AS i_lifestyle
ON products.lifestyle_image_id=i_lifestyle.image_id
LEFT JOIN assets AS a_lifestyle
ON i_lifestyle.asset_id=a_lifestyle.asset_id
-- deepetch join chain
LEFT JOIN images AS i_deepetch
ON products.deepetch_image_id=i_deepetch.image_id
LEFT JOIN assets AS a_deepetch
ON i_deepetch.asset_id=a_deepetch.asset_id
LEFT JOIN (
SELECT product_id, GROUP_CONCAT(range_id) AS range_ids
FROM product_range
GROUP BY product_id
) AS product_range
ON products.product_id=product_range.product_id
LEFT JOIN ranges
ON products.main_demand_range_id=ranges.range_id
LEFT JOIN content AS demand_range_content
ON products.main_demand_range_id=demand_range_content.range_id
AND demand_range_content.site_id='6'
LEFT JOIN (
SELECT
variant_id,
SUBSTRING_INDEX(
GROUP_CONCAT(file_name ORDER BY sort_order ASC, file_name ASC),
',', 1
) AS file_name
FROM variant_images
WHERE deleted_at IS NULL
GROUP BY variant_id
) vi ON products.default_variant_id = vi.variant_id
WHERE products.product_id IN ()
AND products.product_status > '0'
GROUP BY products.product_id
ORDER BY FIELD(products.product_id,)
MySQL Error: 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 ')
AND products.product_status > '0'
GROUP BY products.prod' at line 99
