Example and usage of JOINS in DB2

DB2DatabaseBig Data Analytics

Problem: How will you find the ORDER_ID, TRANSACTION_ID and TRANSACTION_STATUS from ORDERS and TRANSACTIONS DB2 table using joins?

Solution

We can find ORDER_ID, TRANSACTION_ID and TRANSACTION_STATUS from ORDERS and TRANSACTIONS table using the INNER JOIN query.

For example, if we have below 2 ORDERS table.

ORDER_ID
TRANSACTION_ID
ORDER_DATE
Z22345
ITX4489
23-10-2020
Z62998
ITX4311
21-10-2020
Z56902
ITX3120
26-10-2020

 

TRANSACTION_ID
TRANS_AMT
TRANSACTION_STATUS
ITX4489
1128
PAID
ITX4311
2318
PAID
ITX3120
88956
UNPAID
ITX2167
5612
FAILED


We can use an inner join query as below.

Example

SELECT ORDER_ID, TRANSACTION_ID, TRANSACTION_STATUS FROM ORDERS INNER JOIN TRANSACTIONS ON
ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID

The above query will return the result below.

ORDER_ID
TRANSACTION_ID
TRANSACTION_STATUS
Z22345
ITX4489
PAID
Z62998
ITX4311
PAID
Z56902
ITX3120
UNPAID


raja
Published on 30-Nov-2020 14:03:33
Advertisements