Click here to Skip to main content
14,545,215 members
Rate this:
Please Sign up or sign in to vote.
See more:
I'm using the Adventureworks 2014 database and am trying to achieve an output that displays sales on the bike product category by region

I've created an inner join using 4 tables to achieve this but when I execute it it just looks as thought the figures are copied, it doesn't look like they're influencing each other
e.g the sales YTD column is exactly the same for all of the rows 

Below is the code I used

<pre>select p.[ProductSubcategoryID],s.[TerritoryID],d.[ProductID], r.[CountryRegionCode], r.SalesYTD, r.SalesLastYear
from [Sales].[SalesOrderDetail] as d, [Sales].[SalesOrderHeader] as s, [Production].[Product] as p, [Sales].[SalesTerritory] as r
INNER JOIN [Production].[Product] ON [ProductID] = [ProductID]
INNER JOIN [Sales].[SalesOrderHeader] ON [SalesOrderID] = [SalesOrderID]


What I have tried:

select p.[ProductSubcategoryID],s.[TerritoryID],d.[ProductID], r.[CountryRegionCode], r.SalesYTD, r.SalesLastYear
from [Sales].[SalesOrderDetail] as d, [Sales].[SalesOrderHeader] as s, [Production].[Product] as p, [Sales].[SalesTerritory] as r
INNER JOIN [Production].[Product] ON [ProductID] = [ProductID]
INNER JOIN [Sales].[SalesOrderHeader] ON [SalesOrderID] = [SalesOrderID]
Posted
Updated 19-May-20 16:04pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100