An alternative for "NOT IN" in SQL SERVER -

i have 2 tables:

1. deparment table (save data of deparment):

2. accdocsales table (save data of order made department):

now, want select these departments, not showing in accdocsales table per month (this mean department does't have deptcode in accdocsales table per month).

ex (for case):

i used query:

select distinct month(docdate) thang, b20dept.code, b20dept.name b20dept, b30accdocsales s1 year(s1.docdate) = 2014 , b20dept.code not in (     select s2.deptcode      b30accdocsales s2, b20dept      year(s2.docdate) = 2014 , s2.deptcode = b20dept.code      , month(s1.docdate) = month(s2.docdate) ) order month(docdate) 

it working teacher said "not in" in query not acceptable. asked me find way without "in", "not in".

ps: find 1 more problem query. month have department in "deptcode" , month have no row, not show result when run query.

please help.

try solution:

select s.*, d.* b20dept d     cross apply (select distinct year(s.docdate) y, month(s.docdate) thang b30accdocsales s) s     left join (         select year(s.docdate) y, month(s.docdate) thang, s.deptcode         b30accdocsales s         group year(s.docdate), month(s.docdate), s.deptcode) m on m.y = s.y , m.thang = s.thang , m.deptcode = d.code m.deptcode null  order s.y, s.thang 

edit: in query below, can find solution problem, in ps:

declare @year int = 2014 select s.*, d.* b20dept d     cross apply (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) s (thang)     left join (         select month(s.docdate) thang, s.deptcode         b30accdocsales s         year(s.docdate) = @year         group month(s.docdate), s.deptcode) m on m.thang = s.thang , m.deptcode = d.code m.deptcode null union  select month(s.docdate) thang, '', '' b30accdocsales s year(s.docdate) = @year group month(s.docdate) having count(distinct s.deptcode) = (select count(1) b20dept) order s.thang