Oracle SQL Request

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 ?

>Solution :

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;