Below is a sample query to get a stock overview of your all products including those keeping stock by productAttribute. (Nop 3.80 ) Rgds
USE your_nopcommerce_db
;
WITH ConvertedProductAttributeCombination AS
(
SELECT Id,
ProductId,
CONVERT(XML, AttributesXml) AS AttributesAsXml,
StockQuantity,
Sku
FROM [ProductAttributeCombination]
),
ProdAttComb AS
(
SELECT Id,
ProductId,
AttributesAsXml.value('(/Attributes/ProductAttribute/@ID)[1]','int') as ProductAttributeID1,
AttributesAsXml.value('(/Attributes/ProductAttribute/ProductAttributeValue/Value)[1]','int') as ProductAttributeValue1,
StockQuantity,
Sku
FROM ConvertedProductAttributeCombination
)
SELECT Product.Id,
Product.Name,
Product.Sku,
Product.StockQuantity,
Product.Price,
SpecificationAttributeOption.Name,
SpecificationAttribute.Name,
ProductAttribute.Name,
ProductAttributeValue.Name,
ProdAttComb.StockQuantity
FROM dbo.Product
LEFT JOIN dbo.Product_SpecificationAttribute_Mapping ON Product_SpecificationAttribute_Mapping.ProductId = Product.Id
LEFT JOIN dbo.SpecificationAttributeOption ON SpecificationAttributeOption.Id = Product_SpecificationAttribute_Mapping.SpecificationAttributeOptionId
LEFT JOIN dbo.SpecificationAttribute ON SpecificationAttribute.Id = SpecificationAttributeOption.SpecificationAttributeId
LEFT JOIN dbo.Product_ProductAttribute_Mapping ON Product_ProductAttribute_Mapping.ProductId = Product.Id
LEFT JOIN dbo.ProductAttribute ON ProductAttribute.Id = Product_ProductAttribute_Mapping.ProductAttributeId
LEFT JOIN dbo.ProductAttributeValue ON ProductAttributeValue.ProductAttributeMappingId = Product_ProductAttribute_Mapping.Id
LEFT JOIN ProdAttComb ON ProdAttComb.ProductId = Product.Id AND
ProdAttComb.ProductAttributeID1 = Product_ProductAttribute_Mapping.Id AND
ProdAttComb.ProductAttributeValue1 = ProductAttributeValue.Id
Sample Output:
Id Name Sku StockQ Price Name Name Name Name StockQ
54 Gustav - Jeans Stretch 290005 1 179.00 NULL NULL FashionSize XS 1
54 Gustav - Jeans Stretch 290005 1 179.00 NULL NULL FashionSize S 3
54 Gustav - Jeans Stretch 290005 1 179.00 NULL NULL FashionSize M 2
54 Gustav - Jeans Stretch 290005 1 179.00 NULL NULL FashionSize L 2
54 Gustav - Jeans Stretch 290005 1 179.00 NULL NULL FashionSize XL 1
54 Gustav - Jeans Stretch 290005 1 179.00 NULL NULL FashionSize XXL 1