Get the missing months count as zero (0) and populate into the datatable

See more:

C#

SQL

MySQL

DataTable

dataset

HI Here's my sql query

select Month,count(id) transactions from `tbl_test` where id=" + id + " and Month is not NULL and Year = " + Convert.ToInt32(currentYear) + " group by Month;

Month is varchar and id is int I am using mysql.

Populating it into DataTable

DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);

The result I am getting is like

Month transactions 
May     122
December 178

And the result I want is like

Month transactions 
January    0
February   0
March      0
April      0
May        122
June       0
July       0
August     0
September  0
October    0
November   0
December   178

Kindly Help

What I have tried:

I have created another table with all the months and trying to join the 2. But of no use (I guess because of the where clause in the sql statement). Kindly help

Something like this:

select Month, ifnull(count(id), 0) transactions 
    from `tbl_test` 
    inner join my_month_table  as m on m.month_key = `tbl_test`.month_key 
    where id=" + id + " and Month is not NULL and Year = " + Convert.ToInt32(currentYear) + " 
    group by Month;

Quote:

PS: I would prefer not to change the query (1st one without join) and play around the front end coding (C#) to insert into the DataTable.