Solved

Inner join on 2 temp tables results in cross join

Posted on 2008-06-17
10
271 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
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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

912 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

21 Experts available now in Live!

Get 1:1 Help Now