phion-balance-products.sql

SELECT TOP 1000 p.[Name]
      ,p.[Price_Base]
      ,p.[Price_Wholesale]
      ,p.[Price_List]
      ,p.[Price_Site]
      ,p.[Price_Promotional]
      ,p.[StockStatus]
      ,p.[PartNumber]
      ,p.[TagLine]
      ,p.[SmallDescription]
      ,pa_ht.Value AS HightlightTitle
--      ,pa_h.Value AS Hightlights
      ,pa_mk.Value AS MetaKeywords
      ,pa_ld.Value AS LongDesc
      ,pa_5.Value AS ProductIngredients
      ,pa_6.Value AS ProductExpertTestimonial
      ,pa_7.Value AS ProductTestingPractices
      ,pa_8.Value AS ProductBundle
      ,pa_9.Value AS ClubBalancedOverview
      ,pa_10.Value AS ClubBalancedShippingFrequency
      ,pa_11.Value AS ClubBalancedDiscountFactor
      ,pa_12.Value AS ClubBalancedPromotionalAddons
      ,pa_13.Value AS AbouttheAuthor
      ,pa_14.Value AS TableofContents
      ,pa_16.Value AS FormFactor
      ,pa_17.Value AS ProductBundleTitle
--      ,pa_18.Value AS BookDetails
      ,pa_19.Value AS Weight
--      ,pa_21.Value AS ProductAdvantages
      ,pa_22.Value AS LegalDisclaimers
      ,pa_23.Value AS ClosingArgument
      ,pa_24.Value AS ProductPitchLine
      ,pa_25.Value AS ProductMetaTitle
      ,pa_26.Value AS ProductMetaDescription
      ,pa_27.Value AS Instruction
FROM [pHionBalance].[Store].[Product] AS p
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_ht ON p.ProductID = pa_ht.ProductID AND pa_ht.AttributeID = 1
-- LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_h ON p.ProductID = pa_h.ProductID AND pa_h.AttributeID = 2
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_mk ON p.ProductID = pa_mk.ProductID AND pa_mk.AttributeID = 3
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_ld ON p.ProductID = pa_ld.ProductID AND pa_ld.AttributeID = 4
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_5 ON p.ProductID = pa_5.ProductID AND pa_5.AttributeID = 5
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_6 ON p.ProductID = pa_6.ProductID AND pa_6.AttributeID = 6
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_7 ON p.ProductID = pa_7.ProductID AND pa_7.AttributeID = 7
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_8 ON p.ProductID = pa_8.ProductID AND pa_8.AttributeID = 8
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_9 ON p.ProductID = pa_9.ProductID AND pa_9.AttributeID = 9
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_10 ON p.ProductID = pa_10.ProductID AND pa_10.AttributeID = 10
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_11 ON p.ProductID = pa_11.ProductID AND pa_11.AttributeID = 11
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_12 ON p.ProductID = pa_12.ProductID AND pa_12.AttributeID = 12
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_13 ON p.ProductID = pa_13.ProductID AND pa_13.AttributeID = 13
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_14 ON p.ProductID = pa_14.ProductID AND pa_14.AttributeID = 14
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_16 ON p.ProductID = pa_16.ProductID AND pa_16.AttributeID = 16
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_17 ON p.ProductID = pa_17.ProductID AND pa_17.AttributeID = 17
-- LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_18 ON p.ProductID = pa_18.ProductID AND pa_18.AttributeID = 18
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_19 ON p.ProductID = pa_19.ProductID AND pa_19.AttributeID = 19
-- LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_21 ON p.ProductID = pa_21.ProductID AND pa_21.AttributeID = 21
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_22 ON p.ProductID = pa_22.ProductID AND pa_22.AttributeID = 22
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_23 ON p.ProductID = pa_23.ProductID AND pa_23.AttributeID = 23
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_24 ON p.ProductID = pa_24.ProductID AND pa_24.AttributeID = 24
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_25 ON p.ProductID = pa_25.ProductID AND pa_25.AttributeID = 25
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_26 ON p.ProductID = pa_26.ProductID AND pa_26.AttributeID = 26
LEFT JOIN [pHionBalance].[Store].[ProductAttribute] AS pa_27 ON p.ProductID = pa_27.ProductID AND pa_27.AttributeID = 27
WHERE [p].[IsBundle] is NULL