is there any way to do this using minimum no of joins and select?
2 tables are involved in this operation transaction_pci_details and transaction
SELECT
t6.transaction_pci_details_id,
t6.terminal_id,
t6.transaction_no,
t6.transaction_id,
t6.transaction_type,
t6.reversal_flag,
t6.transmission_date_time,
t6.retrivel_ref_no,
t6.card_no,t6.card_type,
t6.expires_on,
t6.transaction_amount,
t6.currency_code,
t6.response_code,
t6.action_code,
t6.message_reason_code,
t6.merchant_id,
t6.auth_code,
t6.actual_trans_amnt,
t6.bal_card_amnt,
t5.sales_person_id
FROM TRANSACTION AS t5
INNER JOIN
(
SELECT
t4.transaction_pci_details_id,
t4.terminal_id,
t4.transaction_no,
t4.transaction_id,
t4.transaction_type,
t4.reversal_flag,
t4.transmission_date_time,
t4.retrivel_ref_no,
t4.card_no,
t4.card_type,
t4.expires_on,
t4.transaction_amount,
t4.currency_code,
t4.response_code,
t4.action_code,
t3.message_reason_code,
t4.merchant_id,
t4.auth_code,
t4.actual_trans_amnt,
t4.bal_card_amnt
FROM
( SELECT*
FROM transaction_pci_details
WHERE message_reason_code LIKE '%OUT%'||
message_reason_code LIKE '%FAILED%' /*we can add date here*/
UNION ALL
SELECT
t2.transaction_pci_details_id,
t2.terminal_id,
t2.transaction_no,
t2.transaction_id,
t2.transaction_type,
t2.reversal_flag,
t2.transmission_date_time,
t2.retrivel_ref_no,
t2.card_no,
t2.card_type,
t2.expires_on,
t2.transaction_amount,
t2.currency_code,
t2.response_code,
t2.action_code,
t2.message_reason_code,
t2.merchant_id,
t2.auth_code,
t2.actual_trans_amnt,
t2.bal_card_amnt
FROM
( SELECT transaction_id
FROM TRANSACTION
WHERE transaction_type_id = 8
) AS t1
INNER JOIN
( SELECT *
FROM transaction_pci_details
WHERE message_reason_code LIKE '%appro%' /*we can add date here*/
) AS t2
ON t1.transaction_id = t2.transaction_id
) AS t3
INNER JOIN
( SELECT*
FROM transaction_pci_details
WHERE action_code LIKE '%REQ%' /*we can add date here*/
) AS t4
ON t3.transaction_pci_details_id - t4.transaction_pci_details_id = 1
) AS t6
ON t5.transaction_id = t6.transaction_id
