show all months in left join, but only current year

Question

I have simple query

SELECT *
FROM `month` 
LEFT JOIN income
ON month.idmonth = income.idmonth

I want to display income every month this year, in every month there is only one income.

I have tried combining with

WHERE YEAR(`created_at`) = YEAR(CURDATE())

but this query only show 1 row

or remove this line:

WHERE YEAR(`created_at`) = YEAR(CURDATE())

table showing

| month | income |

| Januari | 13000 | //2018

| Januari | 14000 | //current year 2019

| Februari | NULL |

| maret | NULL |

| april | NULL |

| mei | NULL |

. . .

| Desember | NULL |

so this is what I want

| month | income |

| Januari | 14000 |

| Februari | NULL |

| maret | NULL |

| april | NULL |

| mei | NULL |

. . .

| Desember | NULL |

I think you are confused about join, please refer to : https://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server — Alpesh Jikadra, Mar 27 '19 at 03:50
there is http://sqlfiddle.com/#!9/585bc5/4 . thanks for helping @AlpeshJikadra — yudistira larung tino, Mar 27 '19 at 04:06

score 0 · Accepted Answer · answered Mar 27 '19 at 04:18

If you are applying where clause on left join then it behave like an intersection operation.

instead of where clause you can use AND condition with your ON criteria

like below query

CREATE TABLE `bulan` (
  `idmonth` int(11) NOT NULL AUTO_INCREMENT,
  `month` varchar(50) NOT NULL,
  PRIMARY KEY (`idmonth`)
);
insert  into `bulan`(`idmonth`,`month`) 
values 
(1,'januari'),
(2,'februari'),
(3,'maret'),
(4,'april'),
(5,'mei'),
(6,'juni'),
(7,'juli'),
(8,'agustus'),
(9,'september'),
(10,'oktober'),
(11,'november'),
(12,'desember');

CREATE TABLE `income` (
  `idincome` int(11) NOT NULL AUTO_INCREMENT,
  `sk` int(11) DEFAULT NULL,
  `idmonth` int(11) DEFAULT NULL,
  `income` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`idincome`)
);

insert  into `income`
(`idincome`,`sk`,`idmonth`,`income`,`created_at`,`updated_at`) 
values
(1,110,1,1300,'2019-03-26 08:01:26','2019-03-26 08:01:26'),
(2,110,1,2400,'2020-03-27 09:57:11',NULL);

Following query give you the expected output.

SELECT bulan.month, income.income
FROM `bulan` 
LEFT JOIN income
ON bulan.idmonth = income.idmonth
and YEAR(`created_at`) = YEAR(CURDATE())

Try this Demo

show all months in left join, but only current year

| month | income |

| month | income |

1 Answers1