DECLARE @warehouse INT;
DECLARE @category VARCHAR(50);
DECLARE @last_sync DATETIME;

SET @warehouse = 1;
SET @category = '9';
SET @last_sync = '2013-01-16 11:19:33.290';

WITH FiltredVariants(variant_id) AS
(
    SELECT
        variant_id
    FROM (
        SELECT
        variant.id AS variant_id,
        -- Conditions
        C1 = CASE WHEN (spec.netto_price > 50) THEN 1 ELSE 0 END, -- Droższe niż 50 PLN
        C4 = CASE WHEN (variant.quantity > 1) THEN 1 ELSE 0 END, -- Ilość w magazynie > 1
        C2 = CASE WHEN (v2i.type = 3 AND v2i.remote_collection_id = '7533') THEN 1 ELSE 0 END,-- Kolor: Czarny
        C5 = CASE WHEN (v2i.type = 3 AND v2i.remote_collection_id = '7528') THEN 1 ELSE 0 END,-- Kolor: Czerwony
        C3 = CASE WHEN (v2i.type = 4 AND v2i.remote_collection_id = '113493') THEN 1 ELSE 0 END,-- Rozmiar: M
        C6 = CASE WHEN (v2i.type = 4 AND v2i.remote_collection_id = '113492') THEN 1 ELSE 0 END-- Rozmiar: L

        FROM xxxx_product_variant AS variant
        INNER JOIN xxxx_product_has_collection_item AS prod2cat ON (prod2cat.warehouse_id = @warehouse) AND (prod2cat.type = 1) AND (prod2cat.remote_collection_id = @category)
        INNER JOIN xxxx_product AS product ON (product.warehouse_id = @warehouse) AND (product.remote_product_id = prod2cat.remote_product_id)
        INNER JOIN xxxx_product_specify AS spec ON (spec.company_id = 1) AND (spec.warehouse_id = @warehouse) AND (spec.remote_product_id = prod2cat.remote_product_id) AND (spec.variant_id = variant.id)
        INNER JOIN xxxx_variant_has_item AS v2i ON (v2i.warehouse_id = @warehouse) AND (v2i.variant_id = variant.id) AND (v2i.remote_product_id = prod2cat.remote_product_id)
        INNER JOIN xxxx_collection_item AS property ON (property.warehouse_id = @warehouse) AND (property.type = v2i.type) AND (property.remote_collection_id = v2i.remote_collection_id)

        WHERE variant.remote_product_id = prod2cat.remote_product_id
          AND (variant.update_date  > @last_sync OR variant.date  > @last_sync OR
               product.update_date  > @last_sync OR product.date  > @last_sync OR
            -- spec.update_date     > @last_sync                               OR
               property.update_date > @last_sync OR property.date > @last_sync OR
               v2i.update_date      > @last_sync OR v2i.date      > @last_sync OR
               prod2cat.update_date > @last_sync OR prod2cat.date > @last_sync)
    )
    AS Conditions
    GROUP BY variant_id

    HAVING   MAX(C1) = 1
        AND  MAX(C4) = 1
        AND (MAX(C2) = 1 OR MAX(C5) = 1)
        AND (MAX(C3) = 1 OR MAX(C6) = 1)
)

SELECT
    variant.remote_product_id AS product_id,
    variant.id AS variant_id,
    product.name,
    product.description,
    variant.quantity,
    spec.netto_price,
    variant.weight,
    variant.ean13,
    product.vat,
    product.is_active AS product_active,
    variant.is_active AS variant_active,

    v2i.remote_collection_id AS property_id,
    v2i.type AS property_type,
    property.name AS property_name,
    property.value AS property_value,
    property.unit AS property_unit,

    manufacturers.remote_collection_id AS manufacturer_id,
    manufacturers.name AS manufacturer_name,
    manufacturers.image_id AS manufacturer_image_id

    FROM FiltredVariants
    INNER JOIN xxxx_product_variant AS variant ON (variant.id = FiltredVariants.variant_id)
    INNER JOIN xxxx_product_has_collection_item AS prod2cat ON (prod2cat.warehouse_id = @warehouse) AND (prod2cat.type = 1) AND (prod2cat.remote_collection_id = @category)
    INNER JOIN xxxx_product AS product ON (product.warehouse_id = @warehouse) AND (product.remote_product_id = prod2cat.remote_product_id)
    LEFT JOIN xxxx_product_specify AS spec ON (spec.company_id = 1) AND (spec.warehouse_id = @warehouse) AND (spec.remote_product_id = prod2cat.remote_product_id) AND (spec.variant_id = variant.id)
    LEFT JOIN xxxx_variant_has_item AS v2i ON (v2i.warehouse_id = @warehouse) AND (v2i.variant_id = variant.id) AND (v2i.remote_product_id = prod2cat.remote_product_id)
    INNER JOIN xxxx_collection_item AS property ON (property.warehouse_id = @warehouse) AND (property.type = v2i.type) AND (property.remote_collection_id = v2i.remote_collection_id)

    LEFT JOIN xxxx_product_has_collection_item AS prod2mf ON (prod2mf.warehouse_id = @warehouse) AND (prod2mf.type = 5) AND (prod2mf.remote_product_id = variant.remote_product_id)
    LEFT JOIN xxxx_collection_item AS manufacturers ON (manufacturers.warehouse_id = @warehouse) AND (manufacturers.type = 5) AND (manufacturers.remote_collection_id = prod2mf.remote_collection_id)

    ORDER BY product_id, variant_id;