SELECT
p.ProductCode AS id
, p.ProductName AS [stripHTML-title]
, IsNull(IsNull(p.Google_Product_Type, pp.Google_Product_Type),
'n/a') AS product_type
, IsNull(IsNull(p.Google_Product_Category, pp.Google_Product_Category),
'n/a') AS google_product_category
, IsNull(p.SalePrice,p.ProductPrice) AS price
, IsNull(p.ProductManufacturer,'n/a') AS brand
, IsNull(p.ProductCondition,'n/a') AS condition
, CONVERT(VARCHAR(10), (GETDATE() + 29),120) AS expiration_date
, p.ProductDescription AS [stripHTML-description]
, 'Config_FullStoreURLConfig_ProductPhotosFolder/' + p.ProductCode
+ '-2T.jpg' AS image_link
, 'Config_FullStoreURLProductDetails.asp?ProductCode='
+ p.ProductCode + '&click=2' AS link
, CAST(IsNull(p.ProductWeight,pp.ProductWeight) as VARCHAR(30)) + ' lb' as shipping_weight
, IsNull(p.Google_Age_Group, pp.Google_Age_Group) as age_group
, IsNull(p.Google_Availability, pp.Google_Availability) as availability
, IsNull(p.Google_Color, pp.Google_Color) as color
, IsNull(p.Google_Gender, pp.Google_Gender) as gender
, IsNull(p.Google_Material, pp.Google_Material) as material
, IsNull(p.Google_Pattern, pp.Google_Pattern) as pattern
, IsNull(p.Google_Size, pp.Google_Size) as size
, IsNull(p.UPC_Code, pp.UPC_Code) as gtin
, IsNull(p.Book_ISBN, pp.Book_ISBN), IsNull(p.EAN, pp.EAN) as mpn
, p.IsChildOfProductCode as item_group_id
FROM vMerchant.Products_Joined p
LEFT OUTER JOIN vMerchant.Products_Joined pp
ON p.IsChildOfProductCode = pp.ProductCode
WHERE ISNULL(p.EnableMultiChildAddToCart, 'N') = 'N'
AND ISNULL(p.EnableOptions_InventoryControl, 'N') = 'N'
AND (ISNULL(p.HideProduct,'N') <> 'Y'
OR ISNULL(p.IsChildOfProductCode,'') <> '')
AND (ISNULL(p.Google_Product_Category, '') <>
'' OR ISNULL(pp.Google_Product_Category, '') <> '')
ORDER BY p.ProductCode