Here are two sql requests
select sum(amount) as personAmountPayments, personId from payments where code = '023' and paiementDate= '01/04/2023' group by payments.personId select sum(amount) as personAmountExtraPayments, personId from extraPayments where code = '23' and paiementDate = '01/04/2023' group by extraPayments.personId
The 2 requests give the sum of payments for each person for april 2023 and code 23.
Some people can be in the first table, while some others are in the second table. But some people can be in both tables.
I would like to list this :
if a person is in one table only : personAmountPayments or personAmountExtraPayments
But if they are in both table : personAmountPayments – personAmountExtraPayments
I would like to add the personId column too of course.
Can you help with the sql request ?
You can use your queries and perform a
FULL OUTER JOIN to connect them when both could be optional:
SELECT COALESCE(p.personId, ep.personId) AS personId, p.personAmountPayments, ep.personAmountExtraPayments FROM ( select personId, sum(amount) as personAmountPayments from payments where code = '023' and paiementDate = DATE '2023-04-01' group by personId ) p FULL OUTER JOIN ( select personId, sum(amount) as personAmountExtraPayments from extraPayments where code = '23' and paiementDate = DATE '2023-04-01' group by personId ) ep ON p.personId = ep.personId;