SQL - Multiple criteria with a LEFT OUTER JOIN

Question

I am trying to do an OUTER JOIN, with multiple join conditions. Here is my query (I will explain issue below):

SELECT ad.*, cp.P_A, cp.P_B, cp.P_C
INTO #AggData3
FROM #AggData2 ad
LEFT OUTER JOIN #CompPriceTemp cp
ON ad.PART=cp.Part_No
 and ad.[Month]=cp.[Month]
 and ad.[Year]=cp.[Year]
GO

For each record in #AggData2, which is average price and volume by month for each part, I want to join the prices of the three competitors (A, B & C). Thus, I want to join based on Part, Month, and Year. Because some competitors don't offer all parts, I am using a LEFT OUTER JOIN. So, the resulting table (#AggData3), should have the exact same number of rows as the initial table (#AggData2), just with the three additional columns with competitor prices.

However, the new table (#AggData3), has ~35,000 more rows than #AggData2.

Any ideas why that is happening, and how to fix my query.

Answer 1

Are you sure that you have only one matching row in CompPriceTemp for every single row in AggData2 ?

Answer 2

Because there are multiple rows in Table #CompPriceTemp that match to one row in #AggData2.

Is there one for each of three competitors perhaps? If that is so, then you need three joins, each to the same table, one for each of the 3 competitors?

But if there is supposed to be one row in #CompPriceTemp for each Month, Year, and product, with three separate columns one column for each competitor, then you have some bad data in there.

Answer 3

Wild guess:

ON ad.PART=cp.Part_No
 and ad.[Month]=cp.[Month]
 and ad.[Year]=cp.[Year]

This query does not uniquely identify rows in CP. Or CP has ~35000 duplicate rows.