gistfile1.sql

/** SELECT VALUES FOR SECTION 'Vanity Tops' AND FILTER 'AVAILABLE_COLORS'  **/ 
SELECT   VALUE        AS NAME,
         VALUE        AS VALUE,
         Count(VALUE) AS COUNT
FROM     (SELECT   product_id,
                   VALUE,
                   position
          FROM     (SELECT pa.product_id                                             AS product_id,
                           pa.position                                               AS position,
                           Substr(pa.VALUE,Instr(pa.VALUE,'~') + 1,Length(pa.VALUE)) AS VALUE
                    FROM   attributes a,
                           product_attributes pa
                    WHERE  pa.attribute_id = a.attribute_id
                           AND pa.product_id IN (SELECT DISTINCT temp_pa.product_id
                                                 FROM   product_attributes temp_pa
                                                        INNER JOIN attributes temp_a
                                                          ON temp_a.attribute_id = temp_pa.attribute_id
                                                 WHERE  (temp_a.NAME = 'SECTION'
                                                         AND (temp_pa.VALUE = 'Vanity Tops'))
                                                 INTERSECT
                                                 SELECT DISTINCT temp_pa.product_id
                                                 FROM   product_attributes temp_pa
                                                        INNER JOIN attributes temp_a
                                                          ON temp_a.attribute_id = temp_pa.attribute_id
                                                 )
                           AND a.NAME = 'AVAILABLE_COLORS')
          WHERE    VALUE <> 'Note 1'
          GROUP BY product_id,
                   VALUE,
                   position)
GROUP BY VALUE,
         position
ORDER BY position ASC,
         VALUE ASC


/** SELECT VALUES FOR SECTION 'Vanity Tops' AND FILTER 'PROJECT_TYPE'  **/
SELECT   VALUE        AS NAME,
         VALUE        AS VALUE,
         Count(VALUE) AS COUNT
FROM     (SELECT   product_id,
                   VALUE,
                   position
          FROM     (SELECT pa.product_id                                             AS product_id,
                           pa.position                                               AS position,
                           Substr(pa.VALUE,Instr(pa.VALUE,'~') + 1,Length(pa.VALUE)) AS VALUE
                    FROM   attributes a,
                           product_attributes pa
                    WHERE  pa.attribute_id = a.attribute_id
                           AND pa.product_id IN (SELECT DISTINCT temp_pa.product_id
                                                 FROM   product_attributes temp_pa
                                                        INNER JOIN attributes temp_a
                                                          ON temp_a.attribute_id = temp_pa.attribute_id
                                                 WHERE  (temp_a.NAME = 'SECTION'
                                                         AND (temp_pa.VALUE = 'Vanity Tops'))
                                                 INTERSECT
                                                 SELECT DISTINCT temp_pa.product_id
                                                 FROM   product_attributes temp_pa
                                                        INNER JOIN attributes temp_a
                                                          ON temp_a.attribute_id = temp_pa.attribute_id
                                                 )
                           AND a.NAME = 'PROJECT_TYPE')
          WHERE    VALUE <> 'Note 1'
          GROUP BY product_id,
                   VALUE,
                   position)
GROUP BY VALUE,
         position
ORDER BY position ASC,
         VALUE ASC