Last active
August 4, 2016 17:08
-
-
Save darrentorpey/03d4e021b42128bc0323beea65a3f110 to your computer and use it in GitHub Desktop.
Helpful Marketplace SQL queries
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
-- ================================== | |
-- Marketplace Brand-Category filters | |
-- ================================== | |
SELECT | |
CASE WHEN brand.name IS NOT NULL THEN brand.name ELSE '[All]' END AS 'Brand', | |
CASE WHEN class.name IS NOT NULL THEN CONCAT_WS(' > ', division.name, department.name, class.name, subclass.name) ELSE '[All]' END AS 'Product Type', | |
channel.name AS 'Channel' | |
FROM marketplace_brandcategoryfilter as filter | |
LEFT OUTER JOIN brands_brand as brand | |
ON (brand.id = filter.brand_id) | |
LEFT OUTER JOIN products_producthierarchy AS subclass | |
ON subclass.id = filter.product_type_id | |
AND subclass.hierarchy_level = 'Sub-Class' | |
LEFT OUTER JOIN products_producthierarchy AS class | |
ON ( | |
class.hierarchy_level = 'Class' | |
AND ( | |
class.id = subclass.parent_id | |
OR ( | |
subclass.parent_id IS NULL | |
AND | |
class.id = filter.product_type_id | |
) | |
) | |
) | |
LEFT OUTER JOIN products_producthierarchy AS department | |
ON department.id = class.parent_id | |
AND department.hierarchy_level = 'Department' | |
LEFT OUTER JOIN products_producthierarchy AS division | |
ON division.id = department.parent_id | |
AND division.hierarchy_level = 'Division' | |
JOIN marketplace_saleschannel AS channel | |
ON channel.id = filter.channel_id | |
ORDER BY | |
brand.name, | |
division.name, | |
department.name, | |
class.name, | |
subclass.name | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment