
CREATE OR REPLACE VIEW `ShopCategoryUrl`
AS
	SELECT
		sc.ShopCategory_ID AS ShopCategory_ID,
		ael.Language_Code AS Language_Code,
		CONCAT('/', GROUP_CONCAT(aelp.ArticleEntry_Language_TextCode SEPARATOR '/')) AS ShopCategoryUrl_Path
	FROM ShopCategory AS sc 
	INNER JOIN ShopCategory AS scp ON (scp.ShopCategory_ID != scp.ShopCategory_ID_Root AND scp.ShopCategory_ID_Root = sc.ShopCategory_ID_Root AND scp.ShopCategory_Left <= sc.ShopCategory_Left AND scp.ShopCategory_Right >= sc.ShopCategory_Right AND (scp.ShopCategory_Level = 1 OR scp.ShopCategory_Level = sc.ShopCategory_Level))
	INNER JOIN ArticleEntry_Language AS ael ON (ael.ArticleEntry_ID = sc.ArticleEntry_ID)
	INNER JOIN ArticleEntry_Language AS aelp ON (aelp.ArticleEntry_ID = scp.ArticleEntry_ID AND aelp.Language_Code = ael.Language_Code)
	WHERE
		sc.ShopCategory_ID != sc.ShopCategory_ID_Root
	GROUP BY sc.ShopCategory_ID
	ORDER BY sc.ShopCategory_ID_Root, sc.ShopCategory_Left
