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

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.

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

You must be make nested query. Firstly, you make union query based on your tables and put in inside query. Then, you make SELECT query and put in outside. Don't forget to give alias table for outside query.

You can try this code :

SELECT * FROM
(
  (SELECT * FROM accounts_tenant_charge)
   UNION
  (SELECT * FROM accounts_tenant_payment)
) a 
ORDER BY a.statement_date ASC

Your tables have different number of columns, therefore you cant union them right away by doing a select * . You have to select an equal number of columns from both tables. Like:

select
  tenant_charge_id as `id`,
  tenant_charge_date as `date`,
  tenant_charge_total_amonunt as `amount`
union
select
  tenant_payment_id,
  tenant_payment_date,
  tenant_payment_amount
order by
  `date` asc