Joining 7 tables using inner joins show inaccurate results

Question

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.

Clearly your additional joins are adding additional rows, resulting in the values being counted too many times - any time you have a 1-many relationship, any join expands the number of rows returned to match. — Dale K, Aug 11 '22 at 23:13
Both result sets show different columns. The first one shows Region, Country, Segment, FinancialYear, YearlySales and Profit. The second one shows Region, Country, Segment, FinancialYear and YearSales. - check edit on post — Testnominiee, Aug 11 '22 at 23:18
Ar OK. Well for some reason, not apparent from the data shown, when you join on product and product cost, you are getting more records than you should. So you need to investigate the raw query i.e. before aggregation to make sense of why that is happening. On the face of it I would expect to see 1 product per salesorderline, and 1 productcost per product, but that clearly isn't the case. — Dale K, Aug 11 '22 at 23:22
From what I see, there are 2 main tables and 3 reference tables here. The main tables are `SalesOrder` and `SalesOrderLineItem`. That should be your base query and the only part that should use `INNER JOIN`. The rest of the tables of `Region`,`Segment` and `SalesRegion` are basically just reference table to return corresponding information, correct? So, maybe those you can do `LEFT JOIN` instead? Or you can make the base join query as a derived table then join it with the other tables? — FanoFN, Aug 11 '22 at 23:49
Possible duplicate of [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, Aug 12 '22 at 00:25
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [mre] [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Aug 12 '22 at 00:26

score -1 · Accepted Answer · answered Aug 11 '22 at 23:38

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

Joining 7 tables using inner joins show inaccurate results

1 Answers1