Solved

Inner join on 2 temp tables results in cross join

Posted on 2008-06-17
10
280 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 250 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 250 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
Independent Software Vendors: 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!

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

627 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