Solved

Inner join on 2 temp tables results in cross join

Posted on 2008-06-17
10
270 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
  • 5
  • 4
10 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Author Comment

by:ximbuex
Comment Utility
Nevermind, I am going to ask another question. This one was poorly worded on my part.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Expert Comment

by:TheVanter
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm using SQL 2000, is the SELECT...FROM (SELECT available in pre 2005 environments?
0
 
LVL 2

Expert Comment

by:TheVanter
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now