Join a table with duplicate entries for a label but not for the ID column

Question

I have the following table Fact_Sales :

ProductSK DateSK SalesAmount SalesNumber 
1         2019   300         150 
2         2019   500         190 
.....

and the following table DimProduct :

ProductSK CategoryLabel 
1         ABC 
2         ABC 
....

I want to calculate the sales by category label but when joining like below the result of the query is generating a cartesian product :

SELECT CategoryLabel, SUM(SalesAmount)
FROM    Fact_Sales,     DimProduct

Avoid implicit joins https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Amira Bedhiafi, Apr 16 '22 at 15:38

score 1 · Answer 1 · answered Apr 16 '22 at 15:45

1

You should use INNER JOIN ON instead:

SELECT CategoryLabel, SUM(F.SalesAmount)
FROM Fact_Sales AS F INNER JOIN DimProduct AS D ON F.ProductSK = D.ProductSK
GROUP BY D.CategoryLabel

answered Apr 16 '22 at 15:45

Liza

33
6

score 0 · Answer 2 · answered Apr 16 '22 at 15:37

You can LEFT join to the distinct values of your Product table :

SELECT DP.CategoryLabel, SUM(FS.SalesAmount)
FROM Fact_Sales AS FS
LEFT JOIN (
SELECT DISTINCT ProductSK,CategoryLabel FROM DimProduct
) AS DP
ON DP.ProductSK=FS.ProductSK
GROUP BY DP.CategoryLabel

Join a table with duplicate entries for a label but not for the ID column

2 Answers2