need result from 2 mysql table

Question

I have below 2 MySQL table name and payment as below data. I need information all unique name of who got payment on December 2022 and January 2023 with other information. I have tried below query but not get the expected result. My expected result is like result table. How can I get result as result table.

SELECT name, Jan23.yearJan, Jan23.Jan,Jan23.paymentJan,Jan22.yearDec, Jan22.Dec,Jan22.paymentDec FROM person, payment 
LEFT JOIN (SELECT year AS yearJan, month AS Jan, payment AS paymentJan FROM payment 
               WHERE year='2023' AND month='1' 
          ) Jan23
          ON `name`.`nid`=`Jan23`.`nid` 
LEFT JOIN (SELECT month AS Dec, year AS yearDec, payment AS paymentDec FROM payment 
               WHERE year='2022' AND month='12' 
          ) Dec22
          ON `name`.`nid`=`Dec22`.`nid`

WHERE name.nid=payment.nid

table : name

SELECT * FROM name;`
    +----------------+
    |nid | person    |
    +----------------+
    | 1  | Root      | 
    | 2  | Alex      | 
    | 3  | Mark      | 
    | 4  | Frank     | 
    | 5  | Christina |
    | 6  | Kery      |
    | 7  | Mikel     |
    | 8  | Jams      |
    | 9  | Lee       |
    | 10 | Carlos    |
    +----------------+

table : payment
SELECT * FROM payment;
+----+----+-----+------+--------+
|pid |nid | year| month| payment|
+----+----+-----+------+--------+
| 1  | 1  | 2023| 1    | 10     |
| 2  | 2  | 2023| 1    | 20     |
| 3  | 3  | 2023| 1    | 20     |
| 4  | 4  | 2023| 1    | 30     |
| 5  | 5  | 2023| 1    | 15     |
| 6  | 1  | 2023| 2    | 10     |
| 7  | 2  | 2023| 2    | 20     |
| 8  | 6  | 2023| 2    | 20     |
| 9  | 8  | 2023| 2    | 20     |
| 10 | 9  | 2023| 2    | 20     |
| 11 | 10 | 2023| 2    | 50     |
| 12 | 2  | 2022| 12   | 20     |
| 13 | 3  | 2022| 12   | 20     |
| 14 | 4  | 2022| 12   | 30     |
| 15 | 8  | 2022| 12   | 20     |
| 16 | 9  | 2022| 12   | 20     |
| 17 | 10 | 2022| 12   | 50     |
+----+----+-----+------+--------+

Result

+-----------+---------+------+-----------+-----+-------+------------+
|name       | yearJan | Jan  |paymentJan | Dec |yearDec| paymentDec |
+-----------+---------+------+-----------+-----+-------+------------+
| Root      | 2023    |  1   |   10      |     |       |            |
| Alex      | 2023    |  1   |   20      | 12  | 2022  |  20        |
| Mark      | 2023    |  1   |   20      | 12  | 2022  |  20        |
| Frank     | 2023    |  1   |   30      | 12  | 2022  |  30        |
| Christina | 2023    |  1   |   15      |     |       |            |
| Jams      |         |      |           | 12  | 2022  |  20        |
| Lee       |         |      |           | 12  | 2022  |  20        |
| Carlos    |         |      |           | 12  | 2022  |  50        |
+-----------+---------+------+-----------+-----+-------+------------+

Answer 1

If I follow you correctly, you can filter and do conditional aggregation:

This gives you the name ids that had at least one payment in Dec 22 or Jan 23, with the total amount for each month:

select nid, 
    sum(case when month = 12 then payment end) payment_dec22,
    sum(case when month =  1 then payment end) payment_jan23
from payment
where (year, month ) in ( (2022, 12), (2023, 1) )
group by nid

We can then bring the names with a join:

select pe.name, pa.*
from person pe 
inner join (
    select nid, 
        sum(case when month = 12 then payment end) payment_dec22,
        sum(case when month =  1 then payment end) payment_jan23
    from payment
    where (year, month ) in ( (2022, 12), (2023, 1) )
    group by nid
) pa on pa.nid = pe.nid