I have a requirement to create a cross sales report(Which combination of the product is most purchased by a customer with it order count), But i want to avoid those orders contains parents and child,Suppose if an order contains P1 ,P2 and P3 products(P2 is the parent of P1) then i want to show the combination like P2 and P3(excluded the child),likewise i have to show the combination of products (excluding parent and child and showing parents only with other products).
So for this i have created a table where i'm updating the order , product details and parent details.But facing an issue to identify the correct parent of product(same child is having multiple parents),So how can i tackle this situation and find the exact and correct parent for each products.
i'm using the table catalog_product_relation for identifying the parent ,child relation(but in this for same child have multiple parents).
Any suggestions/recommendations will be of great help.
Need SQl code for this. Version Magento 1.9, Will be updating to magento 2 soon.