Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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
share|improve this question
8  
Hello ankit welcome to DBA.SE. You've been asked in the comments to your questions on other sites to provide context and details, so please do that. Explain what the query is supposed to do, give some information about the table structure, sizes and indexes. Also please provide the relevant explain plan data, and edit your question to at least include a tag for which database engine you're using (Oracle?) –  Mat Nov 1 '12 at 12:26
    
Hello. Again, you've asked this on Stack Overflow and have been given an answer before the question was closed. Please edit this post with details such as what database software you are using(MSSQL, MySQL, etc.) and info on what your table looks like. Also, reposting the same thing across multiple sites or on the same site is discouraged. –  hexafraction Nov 1 '12 at 12:42
    
i am trying to upload a snapshot so that i can explain properly but the image is not getting uploaded. –  ankit gupta Nov 1 '12 at 13:34
    
basically i have a table say t1, now in this t1 2 consecutive rows are a request-response pair, now i have to extract some type of responses which are timed_out but some information is missing in these responses, so i have to put that information in the result from the corresponding request.......... what i am doing is extracting one table of requests and one table of response from the same t1 and then performing the join.......is there a better way to do it........as after doing all this i have to join this result with another table t2...... so i want to avoid 2 selects and 2 joins –  ankit gupta Nov 1 '12 at 13:39
    
Since your table only has two selects and two joins, removing two of each would leave no query. This would run faster, but... –  Leigh Riffel Nov 1 '12 at 13:53
show 1 more comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.