Product Attributes from an Order

Hi,

I am trying to automate the invoicing of the online orders (we can not use nopcommerce for invoicing ) so I am trying to create an excel file with all the data necessary for creating an invoice. I will be pulling the data from sql. I have all the data I need except the unique SKU if the product has Attribute combinations.

in OrderItem table I get to see the articles from a particular order but they only have product Id which is same for all attributes of that product.
How can I connect the [dbo].[ProductAttributeCombination] which has the unique SKUs (for each combination) with the items from the order?

Thank you!

this should get you close:

SELECT combo.Sku AS "uniqueSKU"
FROM OrderItem item
INNER JOIN Product prod
ON item.ProductId = prod.Id
LEFT JOIN Product_ProductAttribute_Mapping map
ON prod.Id = map.ProductId
LEFT JOIN ProductAttributeCombination combo
ON map.ProductId = combo.ProductId

It worked!!! with slight mistake

SELECT combo.Sku AS "uniqueSKU", Quantity
FROM OrderItem item
INNER JOIN Product prod
ON item.ProductId = prod.Id
LEFT JOIN Product_ProductAttribute_Mapping map
ON prod.Id = map.ProductId
LEFT JOIN ProductAttributeCombination combo
ON map.ProductId = combo.ProductId
WHERE OrderId = 11057;

but the result was doubled
K0048  1
K0049  1
K0048  1
K0049  1

It should be only
K0048  1
K0049  1

Can you tell why?

Try this query

SELECT pac.Sku, oi.Quantity Quantity FROM OrderItem oi 
INNER JOIN ProductAttributeCombination pac ON oi.ProductId = pac.ProductId
WHERE oi.OrderId = {orderId}
Group by pac.Sku, oi.Quantity

Thank you that is it :)  It works,

"Works"?
The query maybe, but I'm not sure you will get the results you expect.  The query returns all possible combinations/SKUs.  But you want just the one(s) that is selected by the customer.  The OrderItem has a column AttributesXml.  Those are the attributes actually tied to the item in the cart.

(Parsing out AttributesXml in SQL could be challenging ;)

You are right , it worked only on the one specific order 11057 when I used it on another order it does not work. I was just going to post a comment  and I saw your reply.

Any suggestions? I tried to follow the syntax but could not understand it fully. Help appretiated!

Oh I get it,  the AttributesXml is unique to Attribute combo and a connection between OrderItem and ProductAttributeCombination

I will try to use that.  Keep you posted.

Good catch New York, sorry I missed that.  I was trying to also get order items that may not have attributes.

This will match on the XML, but it won't get the other items in the order that do not have attributes:

SELECT item.OrderId,prod.sku,item.Quantity,combo.Sku AS "uniqueSKU",item.AttributesXml,combo.AttributesXml
FROM OrderItem item
INNER JOIN Product prod
ON item.ProductId = prod.Id
INNER JOIN Product_ProductAttribute_Mapping map
ON prod.Id = map.ProductId
INNER JOIN ProductAttributeCombination combo
ON map.ProductId = combo.ProductId
WHERE item.OrderId = XXXXXX
AND item.AttributesXML = combo.AttributesXml
AND item.AttributesXml IS NOT NULL

I think I got what I needed I ran 2 separate querries

1. one for the articles with Attributes
2. other for the articles without Attributes

and appended the result of 2nd one to the first one.

Thanks for help!