Joining 7 tables using inner joins show inaccurate results-CodePudding

I have this database arrangement: Click here for the diagram

Below is the query in question:

SELECT TOP 3 Region.RegionID as Region, 
             Country.CountryName as Country, 
             Segment.SegmentName as Segment, 
             YEAR(SalesOrder.SalesOrderDate) as FinancialYear, 
             ROUND(SUM(SalesOrderLineItem.SalePrice),2) AS YearlySales, 
             ROUND(SUM(SalesOrderLineItem.SalePrice-
  (ProductCost.ManufacturingPrice*SalesOrderLineItem.UnitsSold)),2) AS 
            Profit
    FROM (((((((Country
        INNER JOIN Region ON Country.CountryID= Region.CountryID)
        INNER JOIN Segment ON Region.SegmentID= Segment.SegmentID)
        INNER JOIN SalesRegion ON Region.RegionID= 
        SalesRegion.RegionID)
        INNER JOIN SalesOrder ON SalesRegion.SalesRegionID= 
        SalesOrder.SalesRegionID)
        INNER JOIN SalesOrderLineItem ON SalesOrder.SalesOrderID= 
        SalesOrderLineItem.SalesOrderID)
        INNER JOIN Product ON SalesOrderLineItem.ProductID= 
        Product.ProductID)
        INNER JOIN ProductCost ON Product.ProductID= 
        ProductCost.ProductID)

    GROUP BY Region.RegionID, Country.CountryName, Segment.SegmentName, 
         YEAR(SalesOrder.SalesOrderDate)
    ORDER BY YEAR(SalesOrder.SalesOrderDate) ASC, Country.CountryName ASC, 
         Region.RegionID ASC;

When I run this, I get the following values:

Region	Country	Segment	FinancialYear	YearlySales	Profit
2	Canada	Midmarket	2001	3962899.5	1503379.5
4	Canada	Enterprise	2001	357233.1	138413.1
9	Germany	Enterprise	2001	8576141	3353301

However, when I run the same query but remove the inner joins and select that relates to the profit calculation:

SELECT TOP 3 Region.RegionID as Region, Country.CountryName as Country, Segment.SegmentName as Segment, YEAR(SalesOrder.SalesOrderDate) as FinancialYear, ROUND(SUM(SalesOrderLineItem.SalePrice),2) AS YearlySales
FROM (((((Country
INNER JOIN Region ON Country.CountryID= Region.CountryID)
INNER JOIN Segment ON Region.SegmentID= Segment.SegmentID)
INNER JOIN SalesRegion ON Region.RegionID= SalesRegion.RegionID)
INNER JOIN SalesOrder ON SalesRegion.SalesRegionID= SalesOrder.SalesRegionID)
INNER JOIN SalesOrderLineItem ON SalesOrder.SalesOrderID= SalesOrderLineItem.SalesOrderID)

GROUP BY Region.RegionID, Country.CountryName, Segment.SegmentName, YEAR(SalesOrder.SalesOrderDate)
ORDER BY YEAR(SalesOrder.SalesOrderDate) ASC, Country.CountryName ASC, Region.RegionID ASC;

My YearlySales change and I get these values:

Region	Country	Segment	FinancialYear	YearlySales
2	Canada	Midmarket	2001	792579.9
4	Canada	Enterprise	2001	71446.62
9	Germany	Enterprise	2001	1715228.2

Whats happened?

Edit: The reason I am using all these joins is so that I can retrieve the values I want, I need the profit and yearly sales so to do this I need these tables.

CodePudding user response：

ProductCost is per country, so when you join it on you need to also restrict it to the current country.

Note I'm also using the best practice of using table aliases which makes your query a lot easier to read.

INNER JOIN ProductCost PC ON
    PC.ProductID = P.ProductID
    AND PC.CountryId = C.CountryID