In depth SQL question

[John Smith]

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]

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]

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':

select 
  columnA
, columnB
from
  table2
where
  exists
(
  select
    1
  from
    table1
  where
      table2.columnX = table1.columnx 
  and table2.columnY = table1.columnY 
)

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

select 
  columnA
, columnB
from
  table2, table1
where
      table2.columnX = table1.columnx 
  and table2.columnY = table1.columnY 

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

select 
  columnA
, columnB
from
  table2 

[Jan Cumps]

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.