sql server - SQL Subquery Return NULL IN STATEMENT -

i use subquery in in statement, result null. guess problem in data type can't solve this.

this working:

select * qcpcs with(nolock) status = 0   , active = 1     , convert(varchar(8),assigned_to_dept_id) in (10000076, 10000049) 

this not working

select * qcpcs with(nolock) status = 0    , active = 1     , convert(varchar(8),assigned_to_dept_id) in (select department_ids users b                                                   b.[id] = 10000021) 

this subquery's result:

as has been mentioned several times, real solution here not store values in comma separated lists.

that aside, if absolutely cannot change database take above account can use following, highly inefficient , ill-advised:

select * qcpcs with(nolock) status = 0    , active = 1     , patindex('%' + convert(varchar(8),assigned_to_dept_id) + '%'               ,(select department_ids                 users b                 b.[id] = 10000021                )               ) > 0 

or use string splitting function described in great detail here:
http://www.sqlservercentral.com/articles/tally+table/72993/

or fix database design.