Premium Retail Services

SELECT ProdI.[MdmId] AS ProductItemMdmId
      ,ProdI.[Name]
      ,ProdI.[Upc]
      ,ProdI.[BrandId]
      ,ProdI.[Active]
    ,C.[MdmId] AS CountryMdmId
    ,C.[Name] AS CountryName
    ,PS.[SubCategoryId]
    ,SC.[MdmId] AS SubCategoryMdmId
    ,SC.[Name] AS SubCategoryName
    ,CAT.[MdmId] AS CategoryMdmId
    ,CAT.[Name] AS CategoryName
    ,D.[MdmId] AS DepartmentMdmId
    ,D.[Name] AS DepartmentName
    ,D.[HoldingId]
    ,H.[MdmId] AS HoldingMdmId
    ,H.[Name] AS HoldingName
  FROM [dbo].[ProductItem] ProdI
  JOIN [dbo].[Country] C ON ProdI.[CountryId] = C.[Id]
  JOIN [dbo].[ProductSku] PS ON ProdI.[Id] = PS.[ProductItemId]
  JOIN [dbo].[SubCategory] SC ON PS.[SubCategoryId] = SC.[Id]
  JOIN [dbo].[Category] CAT ON SC.[CategoryId] = CAT.[Id]
  JOIN [dbo].[Department] D ON CAT.[DepartmentId] = D.[Id]
  JOIN [dbo].[Holding] H ON D.[HoldingId] = H.[Id]
  WHERE ProdI.[Upc] = '004116700133'
  AND C.[MdmId] = 1
  AND ProdI.[Active] = 1
  AND C.[Active] = 1
  AND PS.[Active] = 1
  AND SC.[Active] = 1
  AND CAT.[Active] = 1
  AND D.[Active] = 1
  AND H.[Active] = 1