Get 12 months of table date for group by and continue taking data from another table with variable date using LEFT JOIN

Question

My table 'a', has a certain number of dates, from January to December.

My table 'b', is a calendar table from 2014 to 2100.

My goal is to give a group by from January to December, however in my table 'a' is variable and may not necessarily exist until December.

Real Example of Problem [DB Fiddle]

SELECT
    a.data_tb1
FROM tabela_com_data_variavel AS a
GROUP BY MONTH(a.data_tb1) 
-- O resultado é variavel não pega 12 meses porque só olha as datas da tabela com datas variavel

How am I trying to pull the date from another table with all the dates

SELECT
    a.data_tb1
FROM tabela_com_data_variavel AS a
LEFT JOIN (
           SELECT b.data_calendario 
           FROM tabela_com_dados AS b
           ) ON a.data_tb1 = b.data_calendario 

GROUP BY MONTH(b.data_calendario )  

That is how to get 12 months of this auxiliary table for the group by and continue taking the data from the table with the data_variable, using LEFT JOIN or otherwise.

Answer 1

Do the following:

SELECT 
MONTH(b.fulldate) AS MES_DATE, YEAR(b.fulldate) AS ANO_DATE, 
COUNT(a.demissao) AS adm_1

FROM dates AS b

LEFT JOIN cadastro_funcionarios_connect AS a
    ON MONTH(a.demissao) = MONTH(b.fulldate)
    AND YEAR(a.demissao) = YEAR(b.fulldate)

GROUP BY

MONTH(b.fulldate), YEAR(b.fulldate);

Remember, always bring the table that contains more data and make a LEFT in which you have less data, you can concatenate the values also to unify the MONTH() with the YEAR(). Adjust the code as needed.