want to optimize this query by removing two SELECT and two JOINS

Question

SELECT 
t3.transaction_pci_details_id,
t3.terminal_id,
t3.transaction_no,
t4.transaction_id,
t3.transaction_type,
t3.reversal_flag,
t3.transmission_date_time,
t4.retrivel_ref_no,
t3.card_no,
t3.card_type,
t4.expires_on,
t3.transaction_amount,
t3.currency_code,
t3.response_code,
t3.action_code,
t3.message_reason_code,
t3.merchant_id,
t3.auth_code,
t3.actual_trans_amnt,
t3.bal_card_amnt,
t3.sales_person_id
FROM
(   *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,
    t1.sales_person_id
    FROM transaction_pci_details AS t2
    INNER JOIN TRANSACTION AS t1
    ON t1.transaction_id= t2.transaction_id
    WHERE  (t1.transaction_type_id = 8 && t2.message_reason_code LIKE '%appro%') || 
        t2.message_reason_code LIKE '%OUT%'|| 
        t2.message_reason_code LIKE '%FAILED%'*
)AS t3
INNER JOIN transaction_pci_details AS t4
ON t3.transaction_pci_details_id - t4.transaction_pci_details_id = 1

Answer 1

Assuming you're trying to remove the self join and get information from the transaction_pci_details tables in one go, you need something like the

LAG(t2.transaction_id) OVER (ORDER BY t2.transaction_pci_details_id),
LAG(t2.expires_on) OVER (ORDER BY t2.transaction_pci_details_id),
LAG(t2.retrivel_ref_no) OVER (ORDER BY t2.transaction_pci_details_id),

Depending on what DBMS you're using (??), is what the exact command will be. Also, depending on your particular data model, you may need to do this before your as the WHERE clause, as having it on the same level would apply it to the functions as well. In general, a self join on a seemingly primary key shouldn't be terrible.