When you are using
comma notation for doing an INNER JOIN, you need to qualify the relationship between the joined tables within the WHERE clause. You have none so the relationship really can get unfiltered like you see.
The INNER JOINs you did add in are not
aliased the same, so they are just acting as additional joins.
And without the join terms being qualified with a table name or an alias.. they are quite ambiguous and you make the DB lookup the FK<==>PK relationship to put it together.
Try this out, using standard INNER JOINs with qualified definitions
SELECT p.ProductSubcategoryID
, s.TerritoryID
, d.ProductID
, r.CountryRegionCode
, r.SalesYTD
, r.SalesLastYear
FROM Sales.SalesOrderDetail d
INNER JOIN Sales.SalesOrderHeader s ON d.SalesOrderID = s.SalesOrderID
INNER JOIN Production.Product p ON d.ProducID = p.ProductID
INNER JOIN Sales.SalesTerritory r ON s.TerritoryID = r.TerritoryID
It would also be helpful to tell us what exactly you are looking for in the query... I don't know the AdventureWorks DB but by looking at the schema of it and your query, I can see why you are getting a lot of values that appear to be duplicates.
You may want to add in a WHERE clause to limit the returns to something that will show (much) fewer results for clarity