Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Inner join on 2 temp tables results in cross join

Posted on 2008-06-17
10
Medium Priority
?
281 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:ximbuex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 21805025
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
 
LVL 2

Assisted Solution

by:TheVanter
TheVanter earned 1000 total points
ID: 21805026
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
 

Author Closing Comment

by:ximbuex
ID: 31468042
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:ximbuex
ID: 21805069
In other words, I want to combine the results of the two tables above with some aggregate functions
0
 

Author Comment

by:ximbuex
ID: 21805108
Nevermind, I am going to ask another question. This one was poorly worded on my part.
0
 
LVL 2

Expert Comment

by:TheVanter
ID: 21805197
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
 
LVL 2

Expert Comment

by:TheVanter
ID: 21805298
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
 

Author Comment

by:ximbuex
ID: 21805428
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
 

Author Comment

by:ximbuex
ID: 21805478
I'm using SQL 2000, is the SELECT...FROM (SELECT available in pre 2005 environments?
0
 
LVL 2

Expert Comment

by:TheVanter
ID: 21805953
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question