As you will have noticed from my previous posts, I'm on steep learning curve with SQL (although the help I received from you guys have made my SQL journey much easier
I have been taking the advice of member called AndyC by building SQL code by understanding the questions and not focus too much on SQL answers from books.
The question from a book states the following:
Find the sales, costs, and gross and net profit for every car sold.
The SQL code answer from the book is as follows:
SELECT DT.MakeName ,DT.ModelName ,DT.SaleDate ,DT.SalePrice ,DT.Cost ,DT.SalePrice - DT.DirectCosts AS GrossProfit FROM (SELECT MK.MakeName ,MD.ModelName ,SA.SaleDate ,SD.SalePrice ,ST.Cost ,SD.LineItemDiscount ,ISNULL(ST.RepairsCost, 0) + ST.PartsCost + ST.TransportInCost AS DirectCosts FROM Data.Make MK INNER JOIN Data.Model MD ON MK.MakeID = MD.MakeID INNER JOIN Data.Stock ST ON ST.ModelID = MD.ModelID INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID INNER JOIN Data.Sales SA ON SA.SalesID = SD.SalesID) DT
The answer from the above code produces 348 records
The following is my SQL code:
SELECT Make.MakeName ,Model.ModelName ,Sales.SaleDate ,SalesDetails.SalePrice ,Stock.Cost ,SalesDetails.SalePrice - SubQuery.DirectCosts AS GrossProfit FROM (SELECT Make.MakeName ,Model.ModelName ,Sales.SaleDate ,Stock.Cost ,ISNULL(Stock.RepairsCost, 0) + Stock.PartsCost + Stock.TransportInCost AS DirectCosts FROM Data.Make INNER JOIN Data.Model ON Make.MakeID = Model.MakeID INNER JOIN Data.Stock ON Model.ModelID = Stock.ModelID INNER JOIN Data.SalesDetails ON Stock.StockCode = SalesDetails.StockID INNER JOIN Data.Sales ON SalesDetails.SalesID = Sales.SalesID) SubQuery ,Data.Make INNER JOIN Data.Model ON Model.MakeID = Make.MakeID INNER JOIN Data.Stock ON Stock.ModelID = Model.ModelID INNER JOIN Data.SalesDetails ON SalesDetails.StockID = Stock.StockCode INNER JOIN Data.Sales ON Sales.SalesID = SalesDetails.SalesID
My Code produces over 1000's records.
Can someone take a look at my code and let me know why my code produces so many records.
Once again, please let me know if you need further information to help me with this issue?