How to get all transaction_email ,transaction status from sql query Magento 2

Question

I am trying to get all transactions,transactions_id,transaction_email,transaction status by SQL query. I found the table where all transactions are stored sales_payment_transaction, sales_order_payment and sales_order

So I can easily get all transactions and 'transactions_id' but how can I get transaction_email,transaction status

SQL query

SELECT spt.transaction_id, spt.txn_id, spt.is_closed, so.customer_email
FROM sales_payment_transaction spt 
INNER JOIN sales_order so on spt.order_id = so.entity_id;

Is that right?

Answer 1

I believe, by transaction_email you meant customer email and by transaction status you meant a way to identify whether order was placed successfully or not.

Customer email is present in sales_order table.

Generally when you click the "Place Order" button, order status is pending and when you make the payment successfully, order status changes to processing. You can get order status from sales_order table.

Here is the sql query for the same:

SELECT spt.order_id, 
    spt.transaction_id,
    spt.txn_id,
    so.customer_email,
    so.status
FROM sales_payment_transaction spt 
JOIN sales_order so ON spt.order_id = so.entity_id