Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

In depth SQL question

 
John Smith
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All:

I am currently using Ibm Db2 8.2 for mainframe.

Is it possible to combine statement1 and statement2 similar to statement 3.
Basically I want to use the sql result in statement 1 for product_code and currency_code and placed it in a where clause for statement 2
STATEMENT 1:
SELECT DISTINCT PRODUCT_CODE,CURRENCY_CODE FROM TABLE1;

STATEMENT 2:
SELECT * FROM ACCOUNT WHERE PRODUCT_CODE=? AND CURRENCY_CODE=?

STATEMENT 3:
SELECT * FROM ACCOUNT WHERE PRODUCT_CODE=? AND CURRENCY_CODE=? (SELECT DISTINCT PRODUCT_CODE,CURRENCY_CODE FROM TABLE1);
)

 
Darryl Burke
Bartender
Posts: 5126
11
Java Netbeans IDE Opera
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John, please BeForthrightWhenCrossPostingToOtherSites.
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14617058
http://www.dbforums.com/db2/1666467-depth-sql-question.html (answered here)
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your statement 3 is a good attempt, but not completely correct.


If your table1 has multiple entries for the same pair of a product_code & currency_code combination, you could use 'where exists':


If the combination product_code & currency_code is unique in table1, then you can use a simple inner join


If it is guaranteed that you always have the product_code & currency_code combinations from table2 available in table1, you can just run the query without using table1

 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Darryl Burke wrote:John, please BeForthrightWhenCrossPostingToOtherSites.
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14617058
http://www.dbforums.com/db2/1666467-depth-sql-question.html (answered here)
There you go! All my effort for nothing.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic