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?
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
It should be only
Can you tell why?
Thank you that is it :) It 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.
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!