Baris Baris - 4 months ago 8
SQL Question

SQL Subquery Return NULL IN STATEMENT

I use subquery in

IN
statement, but the result is null. My guess the problem is in data type but I can't solve this.

This is working:

select *
from qcpcs a with(NOLOCK)
where status = 0
and active = 1
and Convert(varchar(8),assigned_to_dept_id) in (10000076, 10000049)


This is not working

select *
from qcpcs a with(NOLOCK)
where status = 0
and active = 1
and Convert(varchar(8),assigned_to_dept_id) in (select department_ids from users b
where b.[id] = 10000021)


This is the subquery's result:

enter image description here

Answer

As has been mentioned several times, the real solution here is to not store your values in comma separated lists.

That aside, if you absolutely cannot change your database to take the above into account you can use the following, which is highly inefficient and ill-advised:

select *
from qcpcs a with(NOLOCK)
where status = 0 
  and active = 1  
  and patindex('%' + Convert(varchar(8),assigned_to_dept_id) + '%'
              ,(select department_ids
                from users b
                where b.[id] = 10000021
               )
              ) > 0

Or you could use a string splitting function as described in great detail here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

OR YOU COULD FIX YOUR DATABASE DESIGN.

Comments