5-xbyk-content-hub-with-taxonomy-query.sql

SELECT cl.ContentLanguageName
    ,coffee.*
    ,cicd.ProductFieldsName
    ,cicd.ProductFieldsDescription
    ,cicd.ProductFieldsShortDescription
    ,cicd.ProductFieldsImage
    ,processingTags.TagTitle
FROM CMS_ContentItem ci
LEFT JOIN CMS_ContentItemCommonData cicd
    ON cicd.ContentItemCommonDataContentItemID = ci.ContentItemID
LEFT JOIN CMS_ContentItemLanguageMetadata cilm
    ON cicd.ContentItemCommonDataContentLanguageID = cilm.ContentItemLanguageMetadataContentLanguageID
    AND cicd.ContentItemCommonDataContentItemID = cilm.ContentItemLanguageMetadataContentItemID
LEFT JOIN CMS_ContentLanguage cl
    ON cl.ContentLanguageID = cilm.ContentItemLanguageMetadataContentLanguageID
LEFT JOIN CMS_Class c
    ON ci.ContentItemContentTypeID = c.ClassID
LEFT JOIN DancingGoat_Coffee coffee
    ON c.ClassName = 'DancingGoat.Coffee'
    AND cicd.ContentItemCommonDataID = coffee.ContentItemDataCommonDataID
OUTER APPLY (
    SELECT 
        Identifier AS TagGUID
    FROM OPENJSON(coffee.CoffeeProcessing)
    WITH (Identifier UNIQUEIDENTIFIER '$.Identifier')
) AS processingParsedTags
LEFT JOIN CMS_Tag processingTags
    ON processingParsedTags.TagGUID = processingTags.TagGUID
WHERE ci.ContentItemName = 'ElSalvadorFincaSanJose-nr6bglxm'