aspose file tools*
The moose likes JDBC and the fly likes In depth SQL question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "In depth SQL question" Watch "In depth SQL question" New topic
Author

In depth SQL question

John Smith
Ranch Hand

Joined: Aug 21, 2004
Posts: 48
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

Joined: May 03, 2008
Posts: 4642
    
    5

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)


luck, db
There are no new questions, but there may be new answers.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

There you go! All my effort for nothing.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: In depth SQL question