Created
January 27, 2010 16:50
-
-
Save fabiokr/287990 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment