Solved

Inner join on 2 temp tables results in cross join

Posted on 2008-06-17
10
275 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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