In depth sql question

jadeite100 · #1 (**permalink**) 05-14-11, 19:56

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);
)

Stealth_DBA · #2 (**permalink**) 05-14-11, 20:27

jadeite100, Here are a few ways. There are probably others:

Code:

SELECT * 
FROM ACCOUNT 
WHERE (PRODUCT_CODE, CURRENCY_CODE) IN(SELECT PRODUCT_CODE, CURRENCY_CODE 
                                       FROM TABLE1
                                      )

Code:

SELECT * 
FROM ACCOUNT 
WHERE EXISTS (SELECT *
              FROM TABLE1
              WHERE ACCOUNT.PRODUCT_CODE  = TABLE1.PRODUCT_CODE
                AND ACCOUNT.CURRENCY_CODE = TABLE1.CURRENCY_CODE
            )

Code:

SELECT ACCOUNT.*
FROM ACCOUNT
       INNER JOIN
     (SELECT DISTINCT PRODUCT_CODE, CURRENCY_CODE
      TABLE1
     ) AS NEST_TAB
       ON     ACCOUNT.PRODUCT_CODE  = NEST_TAB.PRODUCT_CODE
          AND ACCOUNT.CURRENCY_CODE = NEST_TAB.CURRENCY_CODE

jadeite100 · #3 (**permalink**) 05-14-11, 22:09

Hi:

Thank you for your help!!!
You really save my day!!!

tonkuma · #4 (**permalink**) 05-17-11, 18:11

Another variation(Just for fun.):

Code:

SELECT ACCOUNT.*
 FROM  ACCOUNT
 CROSS JOIN
       LATERAL
       (SELECT DISTINCT
               0
         FROM  TABLE1 T1
         WHERE T1.PRODUCT_CODE  = ACCOUNT.PRODUCT_CODE
           AND T1.CURRENCY_CODE = ACCOUNT.CURRENCY_CODE
       ) AS NEST_TAB

Internet Services

Web Development

Digital Marketing

Consumer Tech