Solved

Inner join on 2 temp tables results in cross join

Posted on 2008-06-17
10
274 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

786 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