从两个不同的表中选择行，但使用php和MySQL按日期排序

Question

I have two tables;

I'm trying to produce a statement (like a bank statement) to show all charges and all payments. The charges and payments need to be displayed in date order based on the statement_date field.

I'm pretty sure I need to use 'unions' but I just can't seem to find a solution.

My effort so far is;

$statement_sql = "(SELECT * FROM accounts_tenant_charge)
UNION
(SELECT * FROM accounts_tenant_payment)
ORDER BY statement_date";

Thanks in advance.

Answer 1

Thanks for your help. The following query worked for me;

SELECT tenant_charge_date as statement_date, tenant_charge_id as reference, tenant_charge_total_amount as debit, NULL as credit, 'Charge' as type FROM accounts_tenant_charge
UNION ALL
SELECT tenant_payment_date as statement_date, tenant_payment_id as reference, NULL as debit, tenant_payment_amount as credit, 'Payment' as type FROM accounts_tenant_payment
ORDER BY statement_date