I usually use a strategy (vision) to solve or construct sql queries in order to get data from database, but i'm afraid that strategy could lead me to wrong sql scripts generating wrong results.
I discovered that trying to solve the problem described next.
" Working on Northwind demo database: I want to obtain the total amount of freight paid/defined for orders in which (order details) there are products from supplier '%Pavlova%' ".
So as i found the correct answer or script, this topic is intended as a kind of survey in order to know if people commit the same error as i did and then, analyzing the problem may develop a "rule" to construct correct sql scripts to avoid that kind of error.
Then please - what is the sql script you develop to solve the problem descrived?
And please do not comment anything, just put the script. After getting some replies then I will point to correct answer and ask you for comments regarding my initial mistake.
And of course try not to look to others code cause it may lead you to write the wrong or right query ;-)
Thanks a lot for your time on this.
PD. Attached is the .jpg of necessary part of Northwind model.
SELECT SUM(Freight) AS total_freight
FROM Suppliers AS s
JOIN Products AS p
ON p.SupplierID = s.SupplierID
JOIN OrderDetails AS od
ON od.ProductID = p.ProductID
WHERE s.CompanyName LIKE '%Pavlova%'
) AS dt
JOIN Orders AS o
ON o.OrderID = dt.OrderID