CODEWITHSUNDEEP
mysqlsubqueryleft-join
doblank
doblank

Reputation: 1

Mysql joint subsquery how to show result only same month and year and null

i have two table

input_table

+--------+-----------+------------+
|     id | id_user   | input_date |        
+--------+-----------+------------+
|      1 |         A | 2020-09-18 | 
|      2 |         B | 2020-09-18 |
|      3 |         C | 2020-09-18 | 
|      4 |         D | 2020-09-18 | 
|      5 |         E | 2020-09-18 | 
|      6 |         A | 2001-09-02 |
+--------+-----------+------------+

paid_table

+--------+-----------+------------+
|     id | id_user   | paid_date  |
+--------+-----------+------------+
|      1 |         A | 2020-09-19 |
|      2 |         B | 2020-09-20 |
|      3 |         C | 2020-09-21 |
|      4 |         A | 2001-09-19 |
+--------+-----------+------------+

What i want for the result is where only show same month and year input_date and paid_date where the month is 09 and year 2020 and result should like this table

result table

+--------+-----------+------------+--------------+
|     id | id_user   | input_date | paid_date    |
+--------+-----------+------------+--------------+
|      1 |         A | 2020-09-18 | 2020-09-19   |
|      2 |         B | 2020-09-18 | 2020-09-20   |
|      3 |         C | 2020-09-18 | 2020-09-21   |
|      4 |         D | 2020-09-18 |   null       |
|      5 |         E | 2020-09-18 |   null       |
+--------+-----------+------------+--------------+

i already try query like this but not satisfied me

SELECT u.id, u. id_user, u. input_date ,bd. paid_date, bd. id_user

FROM tabel_tunggakan u 

LEFT JOIN paid_table bd 

ON u. id_user = bd. id_user 

WHERE u. id_user
IN (select id_user from paid_table where MONTH (paid_date) ='09’  AND YEAR( paid_date  )='2020')

can anyone pointed me the way i should

Upvotes: 0

Views: 36

Answers (1)

Akina
Akina

Reputation: 42622

If each row in paid_table has at least one according row in input_table (the same user in the same year/month) then

SELECT id_user, t1.input_date, t2.paid_date
FROM ( SELECT id_user, MAX(input_date) input_date
       FROM input_table
       WHERE MONTH(input_date) = 9 AND YEAR(input_date) = 2020
       GROUP BY input_date) t1
LEFT JOIN ( SELECT id_user, MAX(paid_date) paid_date
            FROM paid_table
            WHERE MONTH(paid_date) = 9 AND YEAR(paid_date) = 2020
            GROUP BY paid_date ) t2 USING (id_user)

If not then

SELECT t0.id_user, t1.input_date, t2.paid_date
FROM ( SELECT id_user
       FROM input_table
       WHERE MONTH(input_date) = 9 AND YEAR(input_date) = 2020
     UNION
       SELECT id_user
       FROM paid_table
       WHERE MONTH(paid_date) = 9 AND YEAR(paid_date) = 2020 ) t0
LEFT JOIN ( SELECT id_user, MAX(input_date) input_date
            FROM input_table
            WHERE MONTH(input_date) = 9 AND YEAR(input_date) = 2020
            GROUP BY input_date) t1 ON t0.id_user = t1.id_user
LEFT JOIN ( SELECT id_user, MAX(paid_date) paid_date
            FROM paid_table
            WHERE MONTH(paid_date) = 9 AND YEAR(paid_date) = 2020
            GROUP BY paid_date ) t2 ON t0.id_user = t1.id_user

If there exists more than one row for a user in a month then last date is selected in both queries.

Upvotes: 2

Related Questions