To Clarify: My aim is to eliminate the Cartesian product. How do I do this?
I'm a bit stuck with a query. I have to do a P&L based on orders, but each order can have several different units. Moreover the last inner join (d), causes a Cartesian Product. In the first example, I'm able to avoid that by adding 'DISTINCT' to the SELECT statement.First CodeSELECT DISTINCT a.[ID] ,B.[ID] ,b.[AverageCost] ,b.[PostingFee] ,d.[Commission] ,c.[EbayTransactionFee] ,b.[ShippingCost] ,b.[LineTaxTotal] ,a.[FinalShippingFee] ,b.[ProductRebateValue] ,a.[GrandTotal] ,CONVERT(DECIMAL(18,2),a.[GrandTotal]-(b.[AverageCost] + d.[Commission] + a.[FinalShippingFee])) AS 'Profit' FROM [SC].[dbo].[bvc_Order] a INNER JOIN [SC].[dbo].[bvc_OrderItem] b ON a.ID = b.OrderID INNER JOIN [SC].[dbo].[Product] c ON b.[ProductID] = c.[ID] INNER JOIN [SC].[dbo].[BuyDotComOrders] d ON d.[OrderSourceOrderId] = a.[OrderSourceOrderId]
However, when I add a GROUP BY clause to see the P&L based on orders, the distinct statement doesn't help in the same way.Second CodeSELECT DISTINCT a.[ID] ,SUM(b.[AverageCost]) ,SUM(b.[PostingFee]) ,SUM(d.[Commission]) ,SUM(c.[EbayTransactionFee]) ,SUM(b.[ShippingCost]) -- <--- SUM OR NOT? ,SUM(b.[LineTaxTotal]) ,a.[FinalShippingFee] ,SUM(b.[ProductRebateValue]) ,SUM(a.[GrandTotal]) ,CONVERT(DECIMAL(18,2),a.[GrandTotal]-(b.[AverageCost] + d.[Commission] + a.[FinalShippingFee])) AS 'Profit' FROM [SC].[dbo].[bvc_Order] a INNER JOIN [SC].[dbo].[bvc_OrderItem] b ON a.ID = b.OrderID INNER JOIN [SC].[dbo].[Product] c ON b.[ProductID] = c.[ID] INNER JOIN [SC].[dbo].[BuyDotComOrders] d ON d.[OrderSourceOrderId] = a.[OrderSourceOrderId]GROUP BY a.[ID]
How do I proceed? If I can't figure this out, I have to make each column it's own subquery. Which will do the job, but it's not the smart way to do it.-SergioI use Microsoft SQL 2008