sql server 2005 - Sql query with join to return a part number that exists in one table and does not exist in another table with other field data -

i'm trying return data 1 table not exist in table b data. instance:

table     no_         inventory posting group              1           par              2           new              3           par              4           par  table b     no_         table id       dimension code              1           27             branch              2           27             manufacturer              3           27             department              3           27             branch              5           27             department 

every part no_ exists in table a. table b needs have both "branch" , "department" on separate row. parts have 1 or other or both in table b. i'm trying return results show every part listed in table ones not present "branch" , "department" in table b. may 2 separate queries. here have now, i'm not getting want.

select dd.[no_], dd.[table id], dd.[dimension code], dd.[dimension value code], dd.[value posting], dd.[multi selection action] [item] left outer join [default dimension] dd  on  i.[no_] = dd.[no_]  dd.[dimension code] not in ('branch', 'department') , i.[inventory posting group] = 'par' , dd.[table id] = 27 

try this:

select dd.[no_] [item] left outer join [default dimension] dd on  i.[no_] = dd.[no_] i.[inventory posting group] = 'par'   , dd.[no_] null 

the idea when left join table, missing matching records right table have null values.

edit records don't have particular dimension code, need join table has list of dimensions. if don't have such table, can create fake 1 in sub query:

select i.[no_], d.dimension missing_dimension [item] cross join (select 'department' dimension             union             select 'branch' dimension) d left outer join [default dimension] dd on i.[no_] = dd.[no_]             , dd.[dimension code] = d.dimension i.[inventory posting group] = 'par'   , dd.[no_] null order i.[no_] 

here fiddle.