sql - How to select constant value if the Considered value not found? -

sql - How to select constant value if the Considered value not found? -

first of all, i'm sorry bad title i'm selected question. :)

in database have employee , comment tables seen below. each employee can save his/her activities in comment table , manager can see study of employees activity.

i'm using query select employees activity each month in specific year.

select month , isnull(( select ( firstname + '' + lastname ) dbo.employee employeeid = c.employeeid ), '') [name] , isnull(count(employeeid), 0) [count] dbo.comment c year = 1393 grouping month , employeeid order c.employeeid

and result is:

but want this, if each of employees haven't activity in each month, select row month|name|0 month , employee.

how can that?

update i'm changed query this:

declare @aa table ( m int ) declare @c int= 1 while @c < 13 begin insert @aa ( m ) values ( @c -- m - int ) set @c = @c + 1 end select m , ( select ( firstname + ' ' + lastname ) dbo.employee employeeid = c.employeeid ) [name] , count(commentid) [count] dbo.comment c right bring together @aa on m = month grouping m , employeeid order m

and result is:

but want have result:

below 1 method, using cte , cross bring together employee/month combinations.

with employees ( select employeeid , firstname + '' + lastname name dbo.employee ) ,months ( select month (values(1),(2),(3),(4),(5),(6),(7),(8),(8),(10),(11),(12)) months(month) ) select m.month ,e.name ,(select count(*) dbo.comment c c.employeeid = e.employeeid , c.month = m.month , c.year = 1393 ) count months m cross bring together employees e order month ,name;

sql sql-server entity-framework group-by