Inner join on 2 temp tables results in cross join

Ok, I have two tables, #TEMPSOLDITEMS, #TEMPPURCHITEMS
My query should to join the two tables on the item code.
I'm not getting the expected results and I have no clue why. Please help!

Here is the tables, the query, the results, and the expected results

#TEMPSOLDITEMS:
ITEM_CODE    
#TEMPSOLDITEMS:
 
ITEM_CODE	         SOLD_PRICE	QTY_SHIP
---------         --------- 	--------
XYZ		2400.000000	1.000000
XYZ		2688.000000	1.000000
 
#TEMPPURCHITEMS:
 
ITEM_CODE	         PURCH_PRICE	QTY_REC
--------- 	----------	--------
XYZ		0.000000	         1.000000
XYZ		0.000000	         1.000000
 
My Query:
SELECT
	#TEMPSOLDITEMS.ITEM_CODE,
	#TEMPSOLDITEMS.SOLD_PRICE,
	#TEMPPURCHITEMS.PURCH_PRICE,
	#TEMPSOLDITEMS.QTY_SHIP,
	#TEMPPURCHITEMS.QTY_REC
FROM #TEMPSOLDITEMS
INNER JOIN #TEMPPURCHITEMS ON #TEMPSOLDITEMS.ITEM_CODE = #TEMPPURCHITEMS.ITEM_CODE
 
 
 
 
My Results:
 
ITEM_CODE	 SOLD_PRICE  PURCH_PRICE	QTY_SHIP	QTY_REC
---------	 ----------  ----------	--------	-------
XYZ	2400.000000  0.000000	1.000000	1.000000
XYZ	2688.000000  0.000000	1.000000	1.000000
XYZ	2400.000000  0.000000	1.000000	1.000000
XYZ	2688.000000  0.000000	1.000000	1.000000
 
My EXPECTED Results:
 
ITEM_CODE	  SOLD_PRICE  PURCH_PRICE	QTY_SHIP	QTY_REC
---------	  ----------  -----------	--------	-------
XYZ	  2400.000000 0.000000	1.000000	1.000000
XYZ	  2688.000000 0.000000	1.000000	1.000000

Open in new window

ximbuexAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
It is because you're doing a many to many join...although, you can get around it by doing this:

SELECT DISTINCT
        #TEMPSOLDITEMS.ITEM_CODE,
        #TEMPSOLDITEMS.SOLD_PRICE,
        #TEMPPURCHITEMS.PURCH_PRICE,
        #TEMPSOLDITEMS.QTY_SHIP,
        #TEMPPURCHITEMS.QTY_REC
FROM #TEMPSOLDITEMS
INNER JOIN #TEMPPURCHITEMS ON #TEMPSOLDITEMS.ITEM_CODE = #TEMPPURCHITEMS.ITEM_CODE
0
 
TheVanterConnect With a Mentor Commented:
Just use the DISTINCT command to filter out the duplicates.

SELECT DISTINCT
        #TEMPSOLDITEMS.ITEM_CODE,
        #TEMPSOLDITEMS.SOLD_PRICE,
        #TEMPPURCHITEMS.PURCH_PRICE,
        #TEMPSOLDITEMS.QTY_SHIP,
        #TEMPPURCHITEMS.QTY_REC
FROM #TEMPSOLDITEMS
INNER JOIN #TEMPPURCHITEMS ON #TEMPSOLDITEMS.ITEM_CODE = #TEMPPURCHITEMS.ITEM_CODE

should work
0
 
ximbuexAuthor Commented:
Thanks guys, I split points since the answers had the same time stamp. I now realize that I didn't have a complete question, so maybe if you have a second you could chime back in.
If I run this query, I also get unexpected results, and I know its a logic error in what I'm trying to do:
SELECT DISTINCT
      #TEMPSOLDITEMS.ITEM_CODE,
      AVG(#TEMPSOLDITEMS.SOLD_PRICE) AVG_SOLD,
      AVG(#TEMPPURCHITEMS.PURCH_PRICE) AVG_PURCH,
      SUM(#TEMPSOLDITEMS.QTY_SHIP) QTY_SHIP,
      SUM(#TEMPPURCHITEMS.QTY_REC) QTY_REC
FROM #TEMPSOLDITEMS
INNER JOIN #TEMPPURCHITEMS ON #TEMPSOLDITEMS.ITEM_CODE = #TEMPPURCHITEMS.ITEM_CODE
GROUP BY #TEMPSOLDITEMS.ITEM_CODE
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ximbuexAuthor Commented:
In other words, I want to combine the results of the two tables above with some aggregate functions
0
 
ximbuexAuthor Commented:
Nevermind, I am going to ask another question. This one was poorly worded on my part.
0
 
TheVanterCommented:
I think I see... since you're running averages and sums and it's performing those counts on the full joined table, you're getting incorrect totals.  Nesting the queries should work, give this a shot:
SELECT 
AVG(SOLD) AVG_SOLD,
AVG(PURCH) AVG_PURCH,
SUM(SHIP) QTY_SHIP,
SUM(REC) QTY_REC
 FROM (
SELECT DISTINCT
#TEMPSOLDITEMS.ITEM_CODE,
AVG(#TEMPSOLDITEMS.SOLD_PRICE) SOLD,
AVG(#TEMPPURCHITEMS.PURCH_PRICE) PURCH,
SUM(#TEMPSOLDITEMS.QTY_SHIP) SHIP,
SUM(#TEMPPURCHITEMS.QTY_REC) REC
FROM #TEMPSOLDITEMS
INNER JOIN #TEMPPURCHITEMS ON #TEMPSOLDITEMS.ITEM_CODE = #TEMPPURCHITEMS.ITEM_CODE
GROUP BY #TEMPSOLDITEMS.ITEM_CODE)

Open in new window

0
 
TheVanterCommented:
Oops, I forgot to take out the aggregate functions from the joined query.
This one is what I meant:
SELECT 
CODE,
AVG(SOLD) AVG_SOLD,
AVG(PURCH) AVG_PURCH,
SUM(SHIP) QTY_SHIP,
SUM(REC) QTY_REC
 FROM (SELECT DISTINCT
#TEMPSOLDITEMS.ITEM_CODE CODE,
#TEMPSOLDITEMS.SOLD_PRICE SOLD,
#TEMPPURCHITEMS.PURCH_PRICE PURCH,
#TEMPSOLDITEMS.QTY_SHIP SHIP,
#TEMPPURCHITEMS.QTY_REC REC
FROM #TEMPSOLDITEMS
INNER JOIN #TEMPPURCHITEMS ON #TEMPSOLDITEMS.ITEM_CODE = #TEMPPURCHITEMS.ITEM_CODE
GROUP BY #TEMPSOLDITEMS.ITEM_CODE)

Open in new window

0
 
ximbuexAuthor Commented:
Hmm... doesn't like the nested select in the from clause.

I am able to do what I want by using the distinct select into a temp table, then doing the select with the aggregates off of it, but if I can get the syntax working on the your nested select, that's going to be cleaner. Either way, thanks for the great help.
0
 
ximbuexAuthor Commented:
I'm using SQL 2000, is the SELECT...FROM (SELECT available in pre 2005 environments?
0
 
TheVanterCommented:
It should work...
I think I forgot to provide a table alias for the subquery. Try adding "AS mytemptable" to the end after the closing parenthesis.
0
All Courses

From novice to tech pro — start learning today.