Hello Community,
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?
Regards
Carlton