Helpful Marketplace SQL queries

marketplace_brand-category-filters.sql

-- ==================================
-- 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
;